Creating several variables depending on a group number, using another variable as the input.

[Jorge Cimentada]

Suppose I have this data set:

year	country	hhid	hhinfo	age	childc
2010	AT	111	Mother	56	1
2010	AT	111	Father	54	1
2010	AT	111	Son	17	1
2010	GER	222	Spouse	32	0
2010	GER	222	Husband	32	0
2011	PRT	333	Mother	54	2
2011	PRT	333	Father	55	2
2011	PRT	333	Son	12	2
2011	PRT	333	Daughter	13	2

The first three variables uniquely identify a household (hhid) in a given country and in a given year. The hhinfo identifies the family member inside the household. The age variable presentes the age of each respondent and the childc variable counts the number of children within each household. In household 111 there's 1 child, in household 222 there are no children, and in the third household 2 children live there.

What I'd like is for each mother and father row to have the age of each child. For example, in our toy example above there's a maximum of 2 children within all households. Then I'd like to create two variables, one for the first child and another for the second (possible) child. The first child's variable should have the first child's age repeated within the household, the second child's variable should have the second child's age repeated within the household. Naturally, if a family only has one child, the second variable should be missing, and if the family doesn't have any children both variables should be missing. The desired output should be like this:

year	country	hhid	hhinfo	age	childc	child_1	child_2
2010	AT	111	Mother	56	1	17	.
2010	AT	111	Father	54	1	17	.
2010	AT	111	Son	17	1	17	.
2010	GER	222	Spouse	32	0	.	.
2010	GER	222	Husband	32	0	.	.
2011	PRT	333	Mother	54	2	12	13
2011	PRT	333	Father	55	2	12	13
2011	PRT	333	Son	12	2	12	13
2011	PRT	333	Daughter	13	2	12	13

I believe some of this can be obtained fairly easily with egen. However, I'm not sure how to integrate it in a for loop so that in each new variable creation the name includes the number of the child.

I feel that I'm complicating myself too much and that there's probably an easy solution for this. Any help is appreciated.

Thanks a lot.

[Chris Larkin]

Hi Jorge,

I'm sure others can come up with more eloquent solutions, but I think the below should work

gen progeny = (hhinfo=="Daughter"|hhinfo=="Son"), a(hhinfo)
bysort hhid progeny: gen progenydup = _n, a(progeny)

summ progenydup if progeny==1
local max `r(max)'

forval n = 1/`max'{
gen child`n' =.
replace child`n' = age if progeny==1 & progenydup==`n'
}

drop progeny progenydup

This snippet depends on the only possible values for a child in the hhinfo variable being "Daughter" or "Son", and that there are no leading or lagging spaces; to ensure this is the case, you should tab hhinfo to find all values of the variable that correspond to a child and include them in the first line of the above code, and trim the variable (replace hhinfo = trim(hhinfo)).

Can I also suggest you use dataex (ssc install dataex) when you want to share data on Statlist; it makes it easier to work with

[Nick Cox]

Why is a son aged 12 regarded as child 1 and a daughter aged 13 regarded as child 2?

[Chris Larkin]

Thanks Nick! Good spot. I agree it makes more sense to sort on age (descending) before generating progenydup.

Jorge, to generate more meaningful variables try the below instead

gen progeny = (hhinfo=="Daughter"|hhinfo=="Son"), a(hhinfo)
gsort age
bysort hhid progeny: gen progenydup = _n, a(progeny)

summ progenydup if progeny==1
local max `r(max)'

forval n = 1/`max'{
gen child`n' =.
replace child`n' = age if progeny==1 & progenydup==`n'
}

drop progeny progenydup

[Jorge Cimentada]

Thanks Nick, I failed to explain that. Thanks for the answer Chris. I'm getting en error with the a() option:

option a() not allowed

I'm using Stata13 and I can't find anything on the internet on the a() option.

[Chris Larkin]

Not sure why that is. Just drop it; so just

gen progeny = (hhinfo=="Daughter"|hhinfo=="Son")
bysort hhid progeny: gen progenydup = _n

[Nick Cox]

Chris is using options introduced in Stata 14. But Jorge can find the information on the internet regardless.

http://www.stata.com/help.cgi?whatsnew13to14

67. Existing command generate has new options before() and after() so that the
newly created variable be placed before(existing_var) or
after(existing_var). See [D] generate.

The FAQ Advice does include http://www.statalist.org/forums/help#version

11. What should I say about the version of Stata I use?

The current version of Stata is 14.2. Please specify if you are using an earlier version; otherwise, the answer to your question may refer to commands or features unavailable to you. Moreover, as bug fixes and new features are issued frequently by StataCorp, make sure that you update your Stata before posting a query, as your problem may already have been solved.

[Jorge Cimentada]

Hi Chris,

I ran the code above but I'm still not getting the correct variables. Look at what I obtained:

clear
input int year str3 country int hhid str8 hhinfo byte(age childc) float(child1 child2)
2010 "AT"  111 "Father"   54 1  .  .
2010 "AT"  111 "Mother"   56 1  .  .
2010 "AT"  111 "Son"      17 1 17  .
2010 "GER" 222 "Spouse"   32 0  .  .
2010 "GER" 222 "Husband"  32 0  .  .
2011 "PRT" 333 "Father"   55 2  .  .
2011 "PRT" 333 "Mother"   54 2  .  .
2011 "PRT" 333 "Son"      12 2 12  .
2011 "PRT" 333 "Daughter" 13 2  . 13
end

The problem seems to be that the child1..childN variables are filling out only when progeny == 1 and progenydup == N. The filling out should be for the unique household(country year hhid).

Which by the way, this line of code should be:

 bysort year country hhid progeny: gen progenydup = _n

To uniquely identify each household.

[Nick Cox]

I haven't tried to follow all of Chris' code. This is another approach.

clear
input int year str3 country int hhid str8 hhinfo byte(age childc) float(child1 child2)
2010 "AT"  111 "Father"   54 1  .  .
2010 "AT"  111 "Mother"   56 1  .  .
2010 "AT"  111 "Son"      17 1 17  .
2010 "GER" 222 "Spouse"   32 0  .  .
2010 "GER" 222 "Husband"  32 0  .  .
2011 "PRT" 333 "Father"   55 2  .  .
2011 "PRT" 333 "Mother"   54 2  .  .
2011 "PRT" 333 "Son"      12 2 12  .
2011 "PRT" 333 "Daughter" 13 2  . 13
end

gen ischild = inlist(hhinfo, "Son", "Daughter")
bysort year country hhid ischild (age): gen count = sum(ischild) 
su count, meanonly 
gen thiscase = 0 

quietly forval j = 1/`r(max)' { 
    replace thiscase = count == `j' 
    bysort year country hhid (thiscase age) : gen Child`j' = age[_N] if thiscase[_N] 
}     


list hhid hhinfo count child* Child*, sepby(hhid) 

     +----------------------------------------------------------------------+
     | hhid     hhinfo   count   childc   child1   child2   Child1   Child2 |
     |----------------------------------------------------------------------|
  1. |  111        Son       1        1       17        .       17        . |
  2. |  111     Father       0        1        .        .       17        . |
  3. |  111     Mother       0        1        .        .       17        . |
     |----------------------------------------------------------------------|
  4. |  222     Spouse       0        0        .        .        .        . |
  5. |  222    Husband       0        0        .        .        .        . |
     |----------------------------------------------------------------------|
  6. |  333        Son       1        2       12        .       12       13 |
  7. |  333     Mother       0        2        .        .       12       13 |
  8. |  333     Father       0        2        .        .       12       13 |
  9. |  333   Daughter       2        2        .       13       12       13 |
     +----------------------------------------------------------------------+

[Jorge Cimentada]

This works perfectly. Thanks Nick and Chris, you guys've been really helpful.