Pairing children with their mothers

[sania abbasi]

• Dear Statalist,
  
  I am currently working with a dataset that contains information on all household members. My objective is to pair each child with their corresponding mother. The dataset includes the following key variables:
  • hhid: Household ID
  • pidlink: Personal ID for each individual
  • pid14: Order of household members
  • ar11: Order of the mother among all household members (from pid14)
  I already have a binary variable indicating whether an individual is a child, but I need assistance in using the variables mentioned above to correctly pair each child with their mother. Specifically, I would like to assign the mother’s pidlink to the corresponding child’s row based on the relationship between ar11 and pid14.
  
  Could anyone suggest the most efficient way to achieve this pairing in Stata?
  
  Thank you for your time and assistance.
  
  Best regards,
  Sania Abbasi
  
  
  
  

  	* Example generated by -dataex-. To install: ssc install dataex
  	clear
  	input str8 hhid str10 pidlink double(pid14 ar11)
  	"0010600" "001060001"  1 52
  	"0010600" "001060004"  4  2
  	"0010600" "001060007"  7 52
  	"0010600" "001060008"  8  7
  	"0010600" "001060009"  9  7
  	"0010600" "001060010" 10  7
  	"0010600" "001060011" 11  7
  	"0010651" "001060004"  1 52
  	"0010651" "001065102"  2 51
  	"0010651" "001065103"  3  1
  	"0010651" "001065104"  4  1
  	"0010800" "001080001"  1 52
  	"0010800" "001080003"  3  2
  	"0010800" "001080004"  4  2
  	"0010800" "001080005"  5  2
  	"0010800" "001080006"  6  2
  	"0010800" "001080007"  7  2
  	"0010800" "001080008"  8  2
  	"0010800" "001080009"  9  2
  	"0010800" "001080010" 10  2
  	"0010800" "001080012" 12 51
  	"0010851" "001080003"  1 52
  	"0010851" "001080012"  2 52
  	"0010851" "001085103"  3  2
  	"0010851" "001085104"  4  2
  	"0010851" "001085105"  5  2
  	"0010851" "001085106"  6  2
  	"0010851" "001085107"  7  2
  	"0012200" "001220001"  1 52
  	"0012200" "001220002"  2 51
  	"0012200" "001220003"  3  2
  	"0012200" "001220004"  4  2
  	"0012200" "001220005"  5  2
  	"0012200" "001220006"  6  2
  	"0012200" "001220007"  7  2
  	"0012200" "001220008"  8  2
  	"0012200" "001220009"  9  2
  	"0012200" "001220010" 10  2
  	"0012200" "001220011" 11 51
  	"0012200" "001220013" 13 11
  	"0012200" "001220014" 14  2
  	"0012200" "001220015" 15  2
  	"0012241" "001220003"  1 51
  	"0012241" "001220011"  2 51
  	"0012241" "001220013"  3  2
  	"0012241" "001224104"  4  2
  	"0012241" "001224105"  5  2
  	"0012241" "001224106"  6  2
  	"0012242" "001220008"  1 51
  	"0012251" "001220009"  1 51
  	end
  	label values ar11 ar11
  	label def ar11 51 "51:Did not live in this household", modify
  	label def ar11 52 "52:Dead", modify

[Clyde Schechter]

This is the classic use case for -frame-s.

frame copy default mothers
frlink m:1 hhid ar11, frame(mothers hhid pid14)
frget mother_pidlink = pidlink, from(mothers)

[Nick Cox]

This may help. I used rangestat from SSC. rangestat is a keyword to search in this forum for other examples, which are numerous. Essentially all the credit belongs to the first author, Robert Picard.

The calculation is of calculating a mean, as a way to look up individual values. The mean of just one value is precisely that value, but to get that we need a numeric version of the identifier. That doesn't seem problematic as all the identifiers appear to map one to one to integers.

* Example generated by -dataex-. To install: ssc install dataex
    clear
    input str8 hhid str10 pidlink double(pid14 ar11)
    "0010600" "001060001"  1 52
    "0010600" "001060004"  4  2
    "0010600" "001060007"  7 52
    "0010600" "001060008"  8  7
    "0010600" "001060009"  9  7
    "0010600" "001060010" 10  7
    "0010600" "001060011" 11  7
    "0010651" "001060004"  1 52
    "0010651" "001065102"  2 51
    "0010651" "001065103"  3  1
    "0010651" "001065104"  4  1
    "0010800" "001080001"  1 52
    "0010800" "001080003"  3  2
    "0010800" "001080004"  4  2
    "0010800" "001080005"  5  2
    "0010800" "001080006"  6  2
    "0010800" "001080007"  7  2
    "0010800" "001080008"  8  2
    "0010800" "001080009"  9  2
    "0010800" "001080010" 10  2
    "0010800" "001080012" 12 51
    "0010851" "001080003"  1 52
    "0010851" "001080012"  2 52
    "0010851" "001085103"  3  2
    "0010851" "001085104"  4  2
    "0010851" "001085105"  5  2
    "0010851" "001085106"  6  2
    "0010851" "001085107"  7  2
    "0012200" "001220001"  1 52
    "0012200" "001220002"  2 51
    "0012200" "001220003"  3  2
    "0012200" "001220004"  4  2
    "0012200" "001220005"  5  2
    "0012200" "001220006"  6  2
    "0012200" "001220007"  7  2
    "0012200" "001220008"  8  2
    "0012200" "001220009"  9  2
    "0012200" "001220010" 10  2
    "0012200" "001220011" 11 51
    "0012200" "001220013" 13 11
    "0012200" "001220014" 14  2
    "0012200" "001220015" 15  2
    "0012241" "001220003"  1 51
    "0012241" "001220011"  2 51
    "0012241" "001220013"  3  2
    "0012241" "001224104"  4  2
    "0012241" "001224105"  5  2
    "0012241" "001224106"  6  2
    "0012242" "001220008"  1 51
    "0012251" "001220009"  1 51
    end
    label values ar11 ar11
    label def ar11 51 "51:Did not live in this household", modify
    label def ar11 52 "52:Dead", modify
    
    destring pidlink, gen(num_pidlink)
    
    rangestat wanted=num_pidlink, interval(pid14 ar11 ar11) by(hhid)
    
    list, sepby(hhid)
    
         +--------------------------------------------------------------------------------------+
     |    hhid     pidlink   num_pi~k   pid14                                ar11    wanted |
     |--------------------------------------------------------------------------------------|
  1. | 0010600   001060001    1060001       1                             52:Dead         . |
  2. | 0010600   001060004    1060004       4                                   2         . |
  3. | 0010600   001060007    1060007       7                             52:Dead         . |
  4. | 0010600   001060008    1060008       8                                   7   1060007 |
  5. | 0010600   001060009    1060009       9                                   7   1060007 |
  6. | 0010600   001060010    1060010      10                                   7   1060007 |
  7. | 0010600   001060011    1060011      11                                   7   1060007 |
     |--------------------------------------------------------------------------------------|
  8. | 0010651   001060004    1060004       1                             52:Dead         . |
  9. | 0010651   001065102    1065102       2   51:Did not live in this household         . |
 10. | 0010651   001065103    1065103       3                                   1   1060004 |
 11. | 0010651   001065104    1065104       4                                   1   1060004 |
     |--------------------------------------------------------------------------------------|
 12. | 0010800   001080001    1080001       1                             52:Dead         . |
 13. | 0010800   001080003    1080003       3                                   2         . |
 14. | 0010800   001080004    1080004       4                                   2         . |
 15. | 0010800   001080005    1080005       5                                   2         . |
 16. | 0010800   001080006    1080006       6                                   2         . |
 17. | 0010800   001080007    1080007       7                                   2         . |
 18. | 0010800   001080008    1080008       8                                   2         . |
 19. | 0010800   001080009    1080009       9                                   2         . |
 20. | 0010800   001080010    1080010      10                                   2         . |
 21. | 0010800   001080012    1080012      12   51:Did not live in this household         . |
     |--------------------------------------------------------------------------------------|
 22. | 0010851   001080003    1080003       1                             52:Dead         . |
 23. | 0010851   001080012    1080012       2                             52:Dead         . |
 24. | 0010851   001085103    1085103       3                                   2   1080012 |
 25. | 0010851   001085104    1085104       4                                   2   1080012 |
 26. | 0010851   001085105    1085105       5                                   2   1080012 |
 27. | 0010851   001085106    1085106       6                                   2   1080012 |
 28. | 0010851   001085107    1085107       7                                   2   1080012 |
     |--------------------------------------------------------------------------------------|
 29. | 0012200   001220001    1220001       1                             52:Dead         . |
 30. | 0012200   001220002    1220002       2   51:Did not live in this household         . |
 31. | 0012200   001220003    1220003       3                                   2   1220002 |
 32. | 0012200   001220004    1220004       4                                   2   1220002 |
 33. | 0012200   001220005    1220005       5                                   2   1220002 |
 34. | 0012200   001220006    1220006       6                                   2   1220002 |
 35. | 0012200   001220007    1220007       7                                   2   1220002 |
 36. | 0012200   001220008    1220008       8                                   2   1220002 |
 37. | 0012200   001220009    1220009       9                                   2   1220002 |
 38. | 0012200   001220010    1220010      10                                   2   1220002 |
 39. | 0012200   001220011    1220011      11   51:Did not live in this household         . |
 40. | 0012200   001220013    1220013      13                                  11   1220011 |
 41. | 0012200   001220014    1220014      14                                   2   1220002 |
 42. | 0012200   001220015    1220015      15                                   2   1220002 |
     |--------------------------------------------------------------------------------------|
 43. | 0012241   001220003    1220003       1   51:Did not live in this household         . |
 44. | 0012241   001220011    1220011       2   51:Did not live in this household         . |
 45. | 0012241   001220013    1220013       3                                   2   1220011 |
 46. | 0012241   001224104    1224104       4                                   2   1220011 |
 47. | 0012241   001224105    1224105       5                                   2   1220011 |
 48. | 0012241   001224106    1224106       6                                   2   1220011 |
     |--------------------------------------------------------------------------------------|
 49. | 0012242   001220008    1220008       1   51:Did not live in this household         . |
     |--------------------------------------------------------------------------------------|
 50. | 0012251   001220009    1220009       1   51:Did not live in this household         . |
     +--------------------------------------------------------------------------------------+

[sania abbasi]

This is the classic use case for -frame-s.

frame copy default mothers
frlink m:1 hhid ar11, frame(mothers hhid pid14)
frget mother_pidlink = pidlink, from(mothers)

Thank you so much for your time I will try to use that code

[Nick Cox]

Another application of rangestat (SSC) is counting children.

 rangestat (count) count=num_pidlink, interval(ar11 pid14 pid14) by(hhid)

[sania abbasi]

it pair child with mother but i have other variables that should also be align in same row as mother and child but that is not possible with this code

[Nick Cox]

Sorry, but I can’t follow what you’re asking in #6.

[sania abbasi]

Sorry, but I can’t follow what you’re asking in #6.

i have some variable related to child and some are from mother side i need to pair in such a way that i can use for analysis. first comes the child information and then mothers in same row but with above code it is not possible

[Nick Cox]

So either way #1 is not a guide to your data layout; please give more details with an explicit example.