Linking Fathers' Ages to Sons

[Netane Ben-Porath]

Hello,

I have a data of households. In most households there are fathers and sons. There data is built such that for every household individuals are numbered within the househould by ID. Each child has FATHER variable that says what is the ID of the father within the same household. In addition every Father has AGE variable. What I want to do is to take the AGE variable of each father and link it to all of its children FATHER_AGE.

HOUSEHOLD	ID	FATHER	AGE	FATHER_AGE
1	1	.
1	2	.	50
1	3	2	10	50
2	1	.	80
2	2	.	60
2	3	1	40	80
2	4	3	5	40
3	1	2	2	30
3	2	.	30

FATHER_AGE in the above table is what I am trying to construct based on all the other fields. How could I do this?
Thank you very much!

[Hemanshu Kumar]

* this top bit of code only recreates your sample data; you do not need to copy it
clear
input byte(HOUSEHOLD ID    FATHER    AGE    FATHER_AGE)                    
1    1    .    .    .                        
1    2    .    50    .                    
1    3    2    10    50                    
2    1    .    80    .                    
2    2    .    60    .                    
2    3    1    40    80                    
2    4    3    5    40                    
3    1    2    2    30                    
3    2    .    30    .
end

* take the code from here onwards

preserve
    keep HOUSEHOLD ID AGE
    rename ID FATHER
    rename AGE FATHER_AGE2
    tempfile ages
    save `ages'
restore

merge m:1 HOUSEHOLD FATHER using `ages', keepusing(FATHER_AGE2) keep(1 3) nogen
sort HOUSEHOLD ID

which produces:

. li, noobs sepby(HOUSEHOLD) ab(20)

  +----------------------------------------------------------+
  | HOUSEHOLD   ID   FATHER   AGE   FATHER_AGE   FATHER_AGE2 |
  |----------------------------------------------------------|
  |         1    1        .     .            .             . |
  |         1    2        .    50            .             . |
  |         1    3        2    10           50            50 |
  |----------------------------------------------------------|
  |         2    1        .    80            .             . |
  |         2    2        .    60            .             . |
  |         2    3        1    40           80            80 |
  |         2    4        3     5           40            40 |
  |----------------------------------------------------------|
  |         3    1        2     2           30            30 |
  |         3    2        .    30            .             . |
  +----------------------------------------------------------+

[Netane Ben-Porath]

* this top bit of code only recreates your sample data; you do not need to copy it
clear
input byte(HOUSEHOLD ID FATHER AGE FATHER_AGE)
1 1 . . .
1 2 . 50 .
1 3 2 10 50
2 1 . 80 .
2 2 . 60 .
2 3 1 40 80
2 4 3 5 40
3 1 2 2 30
3 2 . 30 .
end

* take the code from here onwards

preserve
keep HOUSEHOLD ID AGE
rename ID FATHER
rename AGE FATHER_AGE2
tempfile ages
save `ages'
restore

merge m:1 HOUSEHOLD FATHER using `ages', keepusing(FATHER_AGE2) keep(1 3) nogen
sort HOUSEHOLD ID

which produces:

. li, noobs sepby(HOUSEHOLD) ab(20)

+----------------------------------------------------------+
| HOUSEHOLD ID FATHER AGE FATHER_AGE FATHER_AGE2 |
|----------------------------------------------------------|
| 1 1 . . . . |
| 1 2 . 50 . . |
| 1 3 2 10 50 50 |
|----------------------------------------------------------|
| 2 1 . 80 . . |
| 2 2 . 60 . . |
| 2 3 1 40 80 80 |
| 2 4 3 5 40 40 |
|----------------------------------------------------------|
| 3 1 2 2 30 30 |
| 3 2 . 30 . . |
+----------------------------------------------------------+

That works perfectly, thank you very much!

[Nick Cox]

Here is another way to do it without file choreography, using rangestat from SSC, which must be installed before you can use it.


Thanks to Hemanshu Kumar for setting up the data example.

clear
input byte(HOUSEHOLD ID    FATHER    AGE    FATHER_AGE)                    
1    1    .    .    .                        
1    2    .    50    .                    
1    3    2    10    50                    
2    1    .    80    .                    
2    2    .    60    .                    
2    3    1    40    80                    
2    4    3    5    40                    
3    1    2    2    30                    
3    2    .    30    .
end

gen father = cond(FATHER < ., FATHER, 0)

ssc install rangestat 

rangestat wanted=AGE, int(ID father father) by(HOUSEHOLD)

list, sepby(HOUSEHOLD)

  




     +-----------------------------------------------------------+
     | HOUSEH~D   ID   FATHER   AGE   FATHER~E   father   wanted |
     |-----------------------------------------------------------|
  1. |        1    1        .     .          .        0        . |
  2. |        1    2        .    50          .        0        . |
  3. |        1    3        2    10         50        2       50 |
     |-----------------------------------------------------------|
  4. |        2    1        .    80          .        0        . |
  5. |        2    2        .    60          .        0        . |
  6. |        2    3        1    40         80        1       80 |
  7. |        2    4        3     5         40        3       40 |
     |-----------------------------------------------------------|
  8. |        3    1        2     2         30        2       30 |
  9. |        3    2        .    30          .        0        . |
     +-----------------------------------------------------------+