Create birth order using relationship to each other

[Maggie SY Fok]

Hi all,

I have a dataset with personal ID, date of birth, and their relationships with others. I want to create a birth order for each person, with value 1 for the eldest sibling.

In the sample dataset below,

• Person 1 and person 3 are siblings. They live in different household.
• Person 4, 5, and 6 are another sibling pair. They live in the same household.
• Person 2 doesn't have any sibling.

The ideal output should be like this.

pid	hid	dob	sib1	sib2	birth_order
1	111	Jan2000	3	-1	1
2	222	Feb2001	-1	-1	1
3	333	Mar2002	1	-1	2
4	444	Apr2003	5	6	1
5	444	May2004	4	6	2
6	444	Jun2005	4	5	3

Can anyone help?

Thanks a lot!

clear
input float(pid hid) str7 dob float(sib1 sib2)
1 111 "Jan2000" 3 -1
2 222 "Feb2001" -1 -1
3 333 "Mar2002" 1 -1
4 444 "Apr2003" 5 6
5 444 "May2004" 4 6
6 444 "Jun2005" 4 5
end

lab var pid "Personal ID"
lab var hid "Household ID"
lab var dob "Date of birth"
lab var sib1 "Personal ID of sibling 1"
lab var sib2 "Personal ID of sibling 2"
label def sib_lab -1 Inapplicable, modify
label values sib1 sib2 sib_lab

[Xinya Hao]

Hi Maggie,

I hope this works.

clear
input float(pid hid) str7 dob float(sib1 sib2)
1 111 "Jan2000" 3 -1
2 222 "Feb2001" -1 -1
3 333 "Mar2002" 1 -1
4 444 "Apr2003" 5 6
5 444 "May2004" 4 6
6 444 "Jun2005" 4 5
end

lab var pid "Personal ID"
lab var hid "Household ID"
lab var dob "Date of birth"
lab var sib1 "Personal ID of sibling 1"
lab var sib2 "Personal ID of sibling 2"
label def sib_lab -1 Inapplicable, modify
label values sib1 sib2 sib_lab

*** Gen birth_order
    gen ym = monthly(dob, "MY")
    format ym %tm

save "_temp_data.dta", replace
    
    keep pid ym
    ren pid sib1
    ren ym ym_sib1
    save "_temp_data_sib1.dta", replace
    
    ren sib1 sib2
    ren ym_sib1 ym_sib2
    save "_temp_data_sib2.dta", replace

use "_temp_data.dta", clear
    merge m:1 sib1 using "_temp_data_sib1.dta"
    drop if _merge == 2
    drop _merge
    
    merge m:1 sib2 using "_temp_data_sib2.dta"
    drop if _merge == 2
    drop _merge
    
    
    gen birth_order = .
    replace birth_order = 1 if ym <= ym_sib1 & ym <= ym_sib2
    replace birth_order = 2 if (ym > ym_sib1 & ym <= ym_sib2) | (ym <= ym_sib1 & ym > ym_sib2)
    replace birth_order = 3 if ym > ym_sib1 & ym > ym_sib2

* rm temp data
cap rm "_temp_data.dta"
cap rm "_temp_data_sib1.dta"
cap rm "_temp_data_sib2.dta"

[Maggie SY Fok]

Thanks Xinya Hao for your help. I found that rowranks by Nicholas Cox can further simplify the code.

rowranks ym ym_sib1 ym_sib2, gen(rank1-rank3)

The birth order is in rank1.

[Hemanshu Kumar]

Here is another way (modified from #2), using Stata's inbuilt commands. I have used frames to avoid some of the variable renaming required by merge in #2, and I have used reshape to help generalize the code to a case where you have more than two siblings:

frames reset

clear
input float(pid hid) str7 dob float(sib1 sib2)
1 111 "Jan2000" 3 -1
2 222 "Feb2001" -1 -1
3 333 "Mar2002" 1 -1
4 444 "Apr2003" 5 6
5 444 "May2004" 4 6
6 444 "Jun2005" 4 5
end

lab var pid "Personal ID"
lab var hid "Household ID"
lab var dob "Date of birth"
lab var sib1 "Personal ID of sibling 1"
lab var sib2 "Personal ID of sibling 2"
label def sib_lab -1 Inapplicable, modify
label values sib1 sib2 sib_lab

gen ym = monthly(dob, "MY")
format ym %tm

frame put pid dob hid, into(unneeded)
drop dob hid

frame put pid ym, into(people)
frlink m:1 sib1, frame(people pid) gen(sib1_info)
frlink m:1 sib2, frame(people pid) gen(sib2_info)

frget ym, from(sib1_info) prefix(sib1_)
frget ym, from(sib2_info) prefix(sib2_)
drop sib?_info

rename sib? sib?_id

gen sib0_id = pid
rename ym sib0_ym

reshape long sib@_id sib@_ym, i(pid) j(num)

bysort pid (sib_ym): gen sib_bo = _n if sib_id != -1

reshape wide sib@_id sib@_ym sib@_bo, i(pid) j(num)
drop sib0* *_ym

frlink 1:1 pid, frame(unneeded)
frget dob hid, from(unneeded)
drop unneeded

which produces:

. list, sep(0) noobs

  +-----------------------------------------------------------------------+
  | pid        sib1_id   sib1_bo        sib2_id   sib2_bo       dob   hid |
  |-----------------------------------------------------------------------|
  |   1              3         2   Inapplicable         .   Jan2000   111 |
  |   2   Inapplicable         .   Inapplicable         .   Feb2001   222 |
  |   3              1         1   Inapplicable         .   Mar2002   333 |
  |   4              5         2              6         3   Apr2003   444 |
  |   5              4         1              6         3   May2004   444 |
  |   6              4         1              5         2   Jun2005   444 |
  +-----------------------------------------------------------------------+