Creating new variables on the basis of relationship to head, age and wages?

[Guest]

Hi, I want to do two things in STATA but I do not know the commands to it. The data is of two rounds-2005 and 2012

1. I want to create a new variable called headwage using HOURLYWAGE and to ensure that it appears in front of each household member and I want to ensure that for each head the wage is his/her average in both the rounds.
I have tried the following commands: g headwage= HOURLYWAGE if RELATIONSHIPTOHEAD==1
egen headinc=max(headwage),by(IDHH)

However, I do not whether it is correct or not?

2. I want to keep the eldest child in the household using the age variable. However, I cannot figure it out.


IDHH is the unique Household ID

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(RELATIONSHIPTOHEAD AGE) double HOURLYWAGES str10 IDHH
 3 16             . "010204010"
 5 13             . "010203010"
 3 38  25.095785141 "010203010"
 5  1             . "010203010"
 1 45 41.4161186219 "010207010"
 4 36             . "010203010"
 3 11             . "010206010"
 3 22   8.469827652 "010201010"
 3 13             . "010201010"
 3 20             . "010204010"
11  7             . "010204010"
 5  6             . "010203010"
 3 21             . "010207010"
 2 34             . "010202010"
 3 16  9.4060611725 "010206010"
 3 15             . "010204010"
 6 65             . "010207010"
 7 60             . "010203010"
 7 18 35.2909507752 "010205010"
 3  2             . "010205010"
 3 18             . "010206010"
 4 34             . "010203010"
 1 45 71.7997131348 "010204010"
 5  5             . "010203010"
 3 34  25.095785141 "010203010"
 3 13             . "010204010"
 3  7             . "010201010"
 5  6             . "010203010"
 2 25             . "010205010"
 1 70             . "010203010"
 2 37             . "010204010"
 2 60             . "010203010"
 3  4             . "010205010"
 3 14             . "010202010"
 3 15             . "010206010"
 2 40  9.4060611725 "010206010"
 2 40             . "010207010"
 3 13             . "010206010"
 5  3             . "010203010"
 3  9             . "010202010"
 5 10             . "010203010"
 1 27 70.5819015503 "010205010"
 6 66             . "010204010"
 3 15             . "010202010"
 3  5             . "010204010"
 1 36 19.4125137329 "010202010"
 1 50 27.3772220612 "010201010"
 1 45             . "010206010"
 2 45             . "010201010"
 7 32             . "010302010"
 2 65             . "010308010"
 1 40 59.0434799195 "010307010"
 1 37 42.0755500794 "010302010"
 3 31 41.5256690979 "010301010"
 3 33 56.5083541871 "010305010"
 1 50 41.1363639832 "010304010"
 2 57             . "010303010"
 3 20 49.3636360169 "010301010"
 5  7             . "010303010"
 5 12             . "010305010"
 3 14             . "010301010"
 2 65             . "010305010"
 5  0             . "010301010"
 3 15             . "010306010"
 2 55             . "010301010"
 5  1             . "010305010"
 1 80             . "010305010"
 5  1             . "010305010"
 5  2             . "010301010"
 2 34 63.1133232117 "010302010"
 1 60 34.3826332093 "010301010"
 4 32  8.2455730438 "010308010"
 2 50             . "010309010"
 5  5             . "010305010"
 3 30             . "010309010"
 3 20             . "010301010"
 4 31             . "010305010"
 3 24             . "010309010"
 3 17             . "010301010"
 3 15             . "010307010"
 3 19             . "010304010"
 2 39             . "010307010"
 4 30             . "010301010"
 6 58             . "010302010"
 3 30  65.818183899 "010306010"
 5 10             . "010308010"
 3 13             . "010307010"
10 30             . "010302010"
 5  7             . "010305010"
 3 28             . "010309010"
 1 55             . "010306010"
 3 20             . "010301010"
 2 45             . "010304010"
 3 36  5.8787879944 "010308010"
 5  6             . "010306010"
 3 24             . "010306010"
 5  4             . "010308010"
11 70             . "010306010"
 3 40 29.3939399719 "010308010"
 5  8             . "010308010"
end
label values RELATIONSHIPTOHEAD RO4
label def RO4 1 "Head 1", modify
label def RO4 2 "Wife/Husband 2", modify
label def RO4 3 "Son/Daughter 3", modify
label def RO4 4 "Child-in-Law 4", modify
label def RO4 5 "Grandchild 5", modify
label def RO4 6 "Father/Mother 6", modify
label def RO4 7 "Brother/Sister 7", modify
label def RO4 10 "Sib-in-Law 10", modify
label def RO4 11 "Other rel 11", modify

[Clyde Schechter]

Your example data doesn't say anything about two rounds, so without that, I can't be sure how to proceed.

As for the second question, when you say oldest child, do you mean just the oldest son/daughter of the head, or do you also want to include children-in-law? What about grandchildren? What about additional relationships if they happen to be children (i.e. age < 18)--maybe the head has a brother-inlaw or other relative who is a child. For that matter, the spouse might be a child. What do you want to do if a son/daughter is an adult--are they counted? How do you want to handle these situations?

[Guest]

Sir, I have attached a much clear example. I am looking at intergenerational income mobility in India and in India, the head predominately determines the educational and occupational status which in turn affects income. I do not know whether it will be possible to include other children as it may bias the result as majority of the studies have taken the eldest child in the house.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(SURVEY year RELATIONSHIPTOHEAD AGE) double HOURLYWAGES str10 IDHH
1 2005 4 23             . "270703031" 
1 2005 1 37 21.5135135651 "282105030" 
1 2005 5  3             . "231201041" 
1 2005 3 35 35.9292068482 "292405060" 
1 2005 5  7             . "272323070" 
1 2005 5  1             . "201201081" 
1 2005 2 41             . "330303080" 
1 2005 2 26             . "212801090" 
1 2005 2 39 13.4144821167 "281606110" 
1 2005 3 18 21.2352943421 "290301130" 
1 2005 1 42 20.9192199707 "060407150" 
1 2005 4 28             . "080607160" 
1 2005 3 13             . "082603161" 
1 2005 4 18             . "232303221" 
2 2012 3 21            15 "2309040111"
2 2012 1 49             . "2403020221"
2 2012 3 16             . "0965060301"
2 2012 2 51            15 "1306020301"
2 2012 1 21             . "0904020312"
2 2012 3 19             . "2309040321"
2 2012 3 10             . "0820030411"
2 2012 6 77             . "2404070601"
2 2012 2 30 26.0869560242 "3310070601"
2 2012 9 14             . "2118050611"
2 2012 1 42         86.25 "2723020701"
2 2012 2 39             . "2917101001"
2 2012 3 12             . "2920221001"
2 2012 1 60            30 "0805021111"
2 2012 1 47         16.25 "2108111111"
2 2012 1 37             . "0707041201"
2 2012 3 47 49.3150672913 "2407131201"
2 2012 2 57             . "3214071301"
2 2012 5  7             . "0815031411"
2 2012 1 55 20.2857151032 "2401071601"
2 2012 1 45             . "0605021611"
end
label values SURVEY SURVEY
label def SURVEY 1 "IHDS1 1", modify
label def SURVEY 2 "IHDS2 2", modify
label values RELATIONSHIPTOHEAD RO4
label def RO4 1 "Head 1", modify
label def RO4 2 "Wife/Husband 2", modify
label def RO4 3 "Son/Daughter 3", modify
label def RO4 4 "Child-in-Law 4", modify
label def RO4 5 "Grandchild 5", modify
label def RO4 6 "Father/Mother 6", modify
label def RO4 9 "Nephew/Niece 9", modify

[Clyde Schechter]

Well, this gives me a sense of what the data look like. And I have written some code that I think will do what you ask. But it is not possible to test it in the data you show because all of the observations you show are households containing only one person, and generally that person is not the head. So you have a bunch of headless households. The code below requires that each household have one and only one head in each wave of the survey (it can be a different person in the different waves). And it calculates for each household the mean income of the head (averaged over both years). It also requires that the age variable never be missing. It also then retains only those observations corresponding to the oldest direct filial descendant of the head living in the household at the time of the survey. (That might be a different person in each wave if a child moves away or returns home after an absence). If a household has no direct filial descendant in a given year, no observations for that household are kept.

//  VERIFY EACH HOUSEHOLD HAS ONE AND ONLY ONE HEAD AT EACH WAVE
by IDHH SURVEY, sort: egen n_heads = total(RELATIONSHIPTOHEAD == "Head 1":RO4)
by IDHH SURVEY: assert n_heads == 1

//  CREATE VARIABLE WITH MEAN INCOME OF HOUSEHOLD HEAD
by IDHH (year), sort: egen mean_head_income = ///
    mean(cond(RELATIONSHIPTOHEAD == "Head 1":RO4), HOURLYWAGES, .)
   
//  KEEP ONLY THE OLDEST CHILD (DIRECT FILIAL DESCENT) OF THE HEAD
assert !missing(AGE)
by IDHH SURVEY RELATIONSHIPTOHEAD (AGE), sort: ///
    keep if _n == _N /*oldest*/ & RELATIONSHIPTOHEAD == "Son/Daughter 3":RO4

[Guest]

Thank you so much.
I could not figure out the last command.