linking parents and children in the household data

Sang-Jic Lee

Join Date: Nov 2019
Posts: 4

linking parents and children in the household data

06 Nov 2019, 00:43

Hello,
I want to link parents and their children by putting the proper value (id) in variables (father, mother, children1, ....).
Would you help me to do that? Thanks.

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long pid int hhid byte(hmem female age) float(relation father mother children1 children2 children3)
 101  1 1 1 57 1 . . . . .
 102  1 2 0 30 2 . . . . .
 201  2 1 0 47 1 . . . . .
 202  2 2 1 44 3 . . . . .
 203  2 3 1 19 2 . . . . .
 301  3 1 1 36 1 . . . . .
 401  4 1 0 28 1 . . . . .
 402  4 2 1 29 3 . . . . .
 501  5 1 1 24 1 . . . . .
 601  6 1 0 72 1 . . . . .
 602  6 2 1 63 3 . . . . .
 603  6 3 1 31 2 . . . . .
 604  6 4 0 27 2 . . . . .
 701  7 1 1 74 1 . . . . .
 801  8 1 0 26 1 . . . . .
 901  9 1 1 80 1 . . . . .
 902  9 2 1 28 2 . . . . .
 903  9 3 1 26 2 . . . . .
1001 10 1 1 44 1 . . . . .
1101 11 1 0 41 1 . . . . .
1102 11 2 1 49 3 . . . . .
1201 12 1 1 22 1 . . . . .
1301 13 1 0 56 1 . . . . .
1302 13 2 1 49 3 . . . . .
1303 13 3 1 25 2 . . . . .
end
label values female female
label def female 0 "male", modify
label def female 1 "female", modify
label values relation h_relation
label def h_relation 1 "head", modify
label def h_relation 2 "child", modify
label def h_relation 3 "spouse", modify

Tags: None

Andrew Musau

Join Date: Oct 2014
Posts: 10481

06 Nov 2019, 05:03

Here is one method based on #5 of the following thread by @Nick Cox.

Code:

drop father -children3
*FIRST IDENTIFY HOUSEHOLDS WITH KIDS
bys hhid: egen hhwkids= max(relation==2)

*CREATE MOTHER ID VAR (RELATION CAN BE HEADS OR SPOUSES)
bys hhid: egen mother= total((female & relation==1| female & relation==3)*pid) if hhwkids

Apply the same logic to the rest.

Res.:

Code:

. l, sepby(hhid)

     +-----------------------------------------------------------------+
     |  pid   hhid   hmem   female   age   relation   hhwkids   mother |
     |-----------------------------------------------------------------|
  1. |  101      1      1   female    57       head         1      101 |
  2. |  102      1      2     male    30      child         1      101 |
     |-----------------------------------------------------------------|
  3. |  201      2      1     male    47       head         1      202 |
  4. |  202      2      2   female    44     spouse         1      202 |
  5. |  203      2      3   female    19      child         1      202 |
     |-----------------------------------------------------------------|
  6. |  301      3      1   female    36       head         0        . |
     |-----------------------------------------------------------------|
  7. |  401      4      1     male    28       head         0        . |
  8. |  402      4      2   female    29     spouse         0        . |
     |-----------------------------------------------------------------|
  9. |  501      5      1   female    24       head         0        . |
     |-----------------------------------------------------------------|
 10. |  601      6      1     male    72       head         1      602 |
 11. |  602      6      2   female    63     spouse         1      602 |
 12. |  603      6      3   female    31      child         1      602 |
 13. |  604      6      4     male    27      child         1      602 |
     |-----------------------------------------------------------------|
 14. |  701      7      1   female    74       head         0        . |
     |-----------------------------------------------------------------|
 15. |  801      8      1     male    26       head         0        . |
     |-----------------------------------------------------------------|
 16. |  901      9      1   female    80       head         1      901 |
 17. |  902      9      2   female    28      child         1      901 |
 18. |  903      9      3   female    26      child         1      901 |
     |-----------------------------------------------------------------|
 19. | 1001     10      1   female    44       head         0        . |
     |-----------------------------------------------------------------|
 20. | 1101     11      1     male    41       head         0        . |
 21. | 1102     11      2   female    49     spouse         0        . |
     |-----------------------------------------------------------------|
 22. | 1201     12      1   female    22       head         0        . |
     |-----------------------------------------------------------------|
 23. | 1301     13      1     male    56       head         1     1302 |
 24. | 1302     13      2   female    49     spouse         1     1302 |
 25. | 1303     13      3   female    25      child         1     1302 |
     +-----------------------------------------------------------------+

Comment

Sang-Jic Lee

Join Date: Nov 2019

Posts: 4
#3

07 Nov 2019, 00:34

Dear Andrew Musau,

Thank you very much for your kindness. The method you suggested works well. So I am very happy. And the reference you mentioned is also very helpful for me to do further study. But I ran into another problems with creating children id variables. If there are more than two children in the household, how can I create children id variable (e.g first, second, third, .....last)? May I ask your advice for this?
1 like
Comment
Andrew Musau

Join Date: Oct 2014

Posts: 10481
#4

07 Nov 2019, 06:03

You want first to create a variable that ranks children within a household. Below, the rank will be from the youngest to eldest, but you can reverse this by using -age in place of age.

Code:

*GEN RANK VARIABLE bys hhid (age): gen rank=_n if relation==2 bys hhid: egen child1= total((relation==2& rank==1)*pid) if hhwkids bys hhid: egen child2= total((relation==2& rank==2)*pid) if hhwkids
2 likes
Comment
Sang-Jic Lee

Join Date: Nov 2019

Posts: 4
#5

07 Nov 2019, 19:07

Thank you again. It works perfectly. But concerning sorting age descending order, your suggestion does not seem to work.
Did you mean that "bys hhid (-age): gen rank=_n if relation==2"? When I put this command, I have get the message, "- invalid name r(198)."

Anyway, your advice has helped me a lot. I deeply appreciate it.
Comment
Andrew Musau

Join Date: Oct 2014

Posts: 10481
#6

08 Nov 2019, 05:18

Did you mean that "bys hhid (-age): gen rank=_n if relation==2"?

You need to create the variable -age. Stata does not allow you to do what you did. Here are two ways, one which creates such a variable and the second where creating the variable is not necessary. I slightly modify my code in #4 so that the rank starts from 1.

Code:

*CREATING -age VARIABLE gen tag= relation!=2 gen nage=-age bys hhid (tag nage): gen rank=_n if relation==2 bys hhid: egen child1= total((rank==1)*pid) if hhwkids

Code:

*WITHOUT CREATING -age VARIABLE gen tag= relation!=2 gsort hhid tag -age by hhid: gen rank=_n if relation==2 bys hhid: egen child1= total((rank==1)*pid) if hhwkids

Last edited by Andrew Musau; 08 Nov 2019, 05:26.
2 likes
Comment
Sang-Jic Lee

Join Date: Nov 2019

Posts: 4
#7

09 Nov 2019, 01:22

It works. I consider myself lucky to have your help. Thank you very much.
Comment
Matthew Williams

Join Date: Feb 2021

Posts: 199
#8

04 Feb 2022, 02:19

Originally posted by Andrew Musau View Post

You need to create the variable -age. Stata does not allow you to do what you did. Here are two ways, one which creates such a variable and the second where creating the variable is not necessary. I slightly modify my code in #4 so that the rank starts from 1.

Code:

*CREATING -age VARIABLE gen tag= relation!=2 gen nage=-age bys hhid (tag nage): gen rank=_n if relation==2 bys hhid: egen child1= total((rank==1)*pid) if hhwkids

Code:

*WITHOUT CREATING -age VARIABLE gen tag= relation!=2 gsort hhid tag -age by hhid: gen rank=_n if relation==2 bys hhid: egen child1= total((rank==1)*pid) if hhwkids

Dear Andrew,

Thanks for excellent assistance. I am facing the same issues and your code has addressed most of them. I have a follow-up question: based on the data example in #1 and code given by you in this thread, how to generate age of mother and each child?

Thanks.
Comment

Andrew Musau

Join Date: Oct 2014
Posts: 10481

04 Feb 2022, 04:36

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input long pid int hhid byte(hmem female age) float(relation father mother children1 children2 children3)
 101  1 1 1 57 1 . . . . .
 102  1 2 0 30 2 . . . . .
 201  2 1 0 47 1 . . . . .
 202  2 2 1 44 3 . . . . .
 203  2 3 1 19 2 . . . . .
 301  3 1 1 36 1 . . . . .
 401  4 1 0 28 1 . . . . .
 402  4 2 1 29 3 . . . . .
 501  5 1 1 24 1 . . . . .
 601  6 1 0 72 1 . . . . .
 602  6 2 1 63 3 . . . . .
 603  6 3 1 31 2 . . . . .
 604  6 4 0 27 2 . . . . .
 701  7 1 1 74 1 . . . . .
 801  8 1 0 26 1 . . . . .
 901  9 1 1 80 1 . . . . .
 902  9 2 1 28 2 . . . . .
 903  9 3 1 26 2 . . . . .
1001 10 1 1 44 1 . . . . .
1101 11 1 0 41 1 . . . . .
1102 11 2 1 49 3 . . . . .
1201 12 1 1 22 1 . . . . .
1301 13 1 0 56 1 . . . . .
1302 13 2 1 49 3 . . . . .
1303 13 3 1 25 2 . . . . .
end
label values female female
label def female 0 "male", modify
label def female 1 "female", modify
label values relation h_relation
label def h_relation 1 "head", modify
label def h_relation 2 "child", modify
label def h_relation 3 "spouse", modify

drop father -children3
*FIRST IDENTIFY HOUSEHOLDS WITH KIDS
bys hhid: egen hhwkids= max(relation==2)

*CREATE MOTHER ID VAR (RELATION CAN BE HEADS OR SPOUSES)
bys hhid: egen mother= total((female & relation==1| female & relation==3)*pid) if hhwkids
*WITHOUT CREATING -age VARIABLE
gen tag= relation!=2
gsort hhid tag -age
by hhid: gen rank=_n if relation==2
bys hhid: egen child1= total((rank==1)*pid) if hhwkids

bys hhid: egen mage = total((mother==pid)*age) if hhwkids
bys hhid: egen child1age = total((child1==pid)*age) if hhwkids

and so on.

Res.:

Code:

. l pid hhid female age relation hhwkids mother child1 mage child1age, sepby(hhid)

     +-------------------------------------------------------------------------------------+
     |  pid   hhid   female   age   relation   hhwkids   mother   child1   mage   child1~e |
     |-------------------------------------------------------------------------------------|
  1. |  102      1     male    30      child         1      101      102     57         30 |
  2. |  101      1   female    57       head         1      101      102     57         30 |
     |-------------------------------------------------------------------------------------|
  3. |  203      2   female    19      child         1      202      203     44         19 |
  4. |  201      2     male    47       head         1      202      203     44         19 |
  5. |  202      2   female    44     spouse         1      202      203     44         19 |
     |-------------------------------------------------------------------------------------|
  6. |  301      3   female    36       head         0        .        .      .          . |
     |-------------------------------------------------------------------------------------|
  7. |  402      4   female    29     spouse         0        .        .      .          . |
  8. |  401      4     male    28       head         0        .        .      .          . |
     |-------------------------------------------------------------------------------------|
  9. |  501      5   female    24       head         0        .        .      .          . |
     |-------------------------------------------------------------------------------------|
 10. |  603      6   female    31      child         1      602      603     63         31 |
 11. |  604      6     male    27      child         1      602      603     63         31 |
 12. |  601      6     male    72       head         1      602      603     63         31 |
 13. |  602      6   female    63     spouse         1      602      603     63         31 |
     |-------------------------------------------------------------------------------------|
 14. |  701      7   female    74       head         0        .        .      .          . |
     |-------------------------------------------------------------------------------------|
 15. |  801      8     male    26       head         0        .        .      .          . |
     |-------------------------------------------------------------------------------------|
 16. |  902      9   female    28      child         1      901      902     80         28 |
 17. |  903      9   female    26      child         1      901      902     80         28 |
 18. |  901      9   female    80       head         1      901      902     80         28 |
     |-------------------------------------------------------------------------------------|
 19. | 1001     10   female    44       head         0        .        .      .          . |
     |-------------------------------------------------------------------------------------|
 20. | 1102     11   female    49     spouse         0        .        .      .          . |
 21. | 1101     11     male    41       head         0        .        .      .          . |
     |-------------------------------------------------------------------------------------|
 22. | 1201     12   female    22       head         0        .        .      .          . |
     |-------------------------------------------------------------------------------------|
 23. | 1303     13   female    25      child         1     1302     1303     49         25 |
 24. | 1301     13     male    56       head         1     1302     1303     49         25 |
 25. | 1302     13   female    49     spouse         1     1302     1303     49         25 |
     +-------------------------------------------------------------------------------------+

.

Comment

Matthew Williams

Join Date: Feb 2021
Posts: 199

#10

04 Feb 2022, 08:55

Dear Andrew,

Thank you so much for your kind help. Your code works perfectly. If you don't mind, I have an additional question regarding reshaping children data from a wide to a long layout. My problem is that when I reshape the data, information of each household member was doubled (if a household has three children, then their information appear three times, and so on). I am not sure if there is anything wrong with my reshape code. I want to have information of each child in each household appeared once after reshaping. I would appreciate it if you could you take a look at my code.

Code:

clear
input long pid int hhid byte(hmem female age) float(relation father mother children1 children2 children3)
 101  1 1 1 57 1 . . . . .
 102  1 2 0 30 2 . . . . .
 201  2 1 0 47 1 . . . . .
 202  2 2 1 44 3 . . . . .
 203  2 3 1 19 2 . . . . .
 301  3 1 1 36 1 . . . . .
 401  4 1 0 28 1 . . . . .
 402  4 2 1 29 3 . . . . .
 501  5 1 1 24 1 . . . . .
 601  6 1 0 72 1 . . . . .
 602  6 2 1 63 3 . . . . .
 603  6 3 1 31 2 . . . . .
 604  6 4 0 27 2 . . . . .
 701  7 1 1 74 1 . . . . .
 801  8 1 0 26 1 . . . . .
 901  9 1 1 80 1 . . . . .
 902  9 2 1 28 2 . . . . .
 903  9 3 1 26 2 . . . . .
1001 10 1 1 44 1 . . . . .
1101 11 1 0 41 1 . . . . .
1102 11 2 1 49 3 . . . . .
1201 12 1 1 22 1 . . . . .
1301 13 1 0 56 1 . . . . .
1302 13 2 1 49 3 . . . . .
1303 13 3 1 25 2 . . . . .
end
label values female female
label def female 0 "male", modify
label def female 1 "female", modify
label values relation h_relation
label def h_relation 1 "head", modify
label def h_relation 2 "child", modify
label def h_relation 3 "spouse", modify

drop father -children3
*FIRST IDENTIFY HOUSEHOLDS WITH KIDS
bys hhid: egen hhwkids= max(relation==2)

*CREATE MOTHER ID VAR (RELATION CAN BE HEADS OR SPOUSES)
bys hhid: egen mother= total((female & relation==1| female & relation==3)*pid) if hhwkids
*WITHOUT CREATING -age VARIABLE
gen tag= relation!=2
gsort hhid tag -age
by hhid: gen rank=_n if relation==2

bys hhid: egen child1= total((rank==1)*pid) if hhwkids
bys hhid: egen child2= total((rank==2)*pid) if hhwkids

bys hhid: egen mage = total((mother==pid)*age) if hhwkids
bys hhid: egen child1age = total((child1==pid)*age) if hhwkids
bys hhid: egen child2age = total((child2==pid)*age) if hhwkids

    keep pid hhid hmem relation child1 child2 child1age child2age
    reshape long child@ child@age, i(pid) j(new)

Results

Code:

. l pid new hhid hmem relation child childage if relation==2, sepby(hhid)

     +--------------------------------------------------------+
     |  pid   new   hhid   hmem   relation   child   childage |
     |--------------------------------------------------------|
  3. |  102     1      1      2      child     102         30 |
  4. |  102     2      1      2      child       0          0 |
     |--------------------------------------------------------|
  9. |  203     1      2      3      child     203         19 |
 10. |  203     2      2      3      child       0          0 |
     |--------------------------------------------------------|
 23. |  603     1      6      3      child     603         31 |
 24. |  603     2      6      3      child     604         27 |
 25. |  604     1      6      4      child     603         31 |
 26. |  604     2      6      4      child     604         27 |
     |--------------------------------------------------------|
 33. |  902     1      9      2      child     902         28 |
 34. |  902     2      9      2      child     903         26 |
 35. |  903     1      9      3      child     902         28 |
 36. |  903     2      9      3      child     903         26 |
     |--------------------------------------------------------|
 49. | 1303     1     13      3      child    1303         25 |
 50. | 1303     2     13      3      child       0          0 |
     +--------------------------------------------------------+

Last edited by Matthew Williams; 04 Feb 2022, 08:59.

Comment

Andrew Musau

Join Date: Oct 2014

Posts: 10481
#11

05 Feb 2022, 07:03

After the reshape:

Code:

keep if pid==child
1 like
Comment

Rhonda Sharpe

Join Date: Dec 2018
Posts: 3

#12

01 Sep 2022, 19:16

Hello,
I am trying to match the mother to the child. Is there a way to do it using momloc, which is the pernum of the mother? I need to tag the mother and her child?

Thanks,
Rhonda

relate	famrel	serial	momloc	pernum	nchild
head/hou	referenc	1	3	1	0 childr
opposite	spouse	1	0	2	0 childr
parent	other re	1	0	3	1 child
head/hou	referenc	2	0	1	1 child
opposite	spouse	2	0	2	1 child
child	child	2	1	3	0 childr
head/hou	not a fa	3	0	1	0 childr
head/hou	referenc	8	0	1	1 child
child	child	8	3	2	0 childr
opposite	not a fa	8	0	3	1 child
head/hou	not a fa	9	0	1	0 childr
head/hou	not a fa	10	0	1	0 childr
head/hou	not a fa	11	0	1	0 childr
head/hou	not a fa	12	0	1	0 childr
head/hou	referenc	13	0	1	1 child
child	child	13	1	2	1 child
grandchi	other re	13	2	3	0 childr
head/hou	not a fa	15	0	1	0 childr
head/hou	referenc	22	0	1	1 child
opposite	spouse	22	0	2	1 child
child	child	22	1	3	0 childr
head/hou	not a fa	27	0	1	0 childr
head/hou	referenc	28	0	1	3
opposite	spouse	28	0	2	3
child	child	28	1	5	0 childr
child	child	28	1	4	0 childr
child	child	28	1	3	0 childr

Announcement

linking parents and children in the household data

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment