Find birth order

[Nicole Kashani]

Hi,

I have the following dataset and I would like to find which individuals are siblings and find the birth order of each individual. How can I do that?

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id parents_id birth_year year expense)
1 1 2001 2020  348.8717
1 1 2001 2019  266.8857
1 1 2001 2018  136.6463
1 1 2001 2021 28.556866
1 1 2001 2022  868.9333
1 1 2001 2023  350.8549
1 1 2001 2024 71.105095
1 1 2001 2016 323.36795
2 5 2009 2009  555.1032
2 5 2009 2010   875.991
2 5 2009 2011 204.70947
2 5 2009 2012  892.7587
3 1 1998 2003  584.4658
3 1 1998 2001  369.7791
3 1 1998 2004  850.6309
4 7 2003 2009  391.3819
4 7 2003 2010 119.66132
4 7 2003 2011  754.2434
4 7 2003 2012  695.0234
5 5 2007 2011  686.6152
5 5 2007 2013  931.9346
5 5 2007 2015  454.8882
end

------------------ copy up to and including the previous line ------------------

[Rich Goldstein]

what is the difference in meaning between "id" and "parents_id"? is "expense" relevant to your questions? if so, how?

[Nicole Kashani]

what is the difference in meaning between "id" and "parents_id"? is "expense" relevant to your questions? if so, how?

Hi,

id is the individual's id and parent's id is an id for the pair of mother and father of the individual.
For the example I provided here, I added a random variable "expense" just because I wanted to show I have a variable that has variation within an individual and I would not want to keep only one observation per individual. Hope it makes sense!

[Daniel Feenberg]

See https://www.stata.com/support/faqs/d...ithin%20oldid.

sort by parents_id birth_year
by parents_id: generate birth_order=_n

It would be hard to deal with twins perfectly. Also divorce could cause problems.

[Nick Cox]

Here is some technique.

What can be done:

Use each individual just once in assigning birth order

What is harder:

What to do with twins and other multiple births in the same year.

What to do with twins and other multiple births in two years (first birth say on Dec 31, last birth on 1 Jan).

What to do when siblings are born in the same year but from different pregnancies (say one in Jan, another in December of same year).

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id parents_id birth_year year expense)
1 1 2001 2020  348.8717
1 1 2001 2019  266.8857
1 1 2001 2018  136.6463
1 1 2001 2021 28.556866
1 1 2001 2022  868.9333
1 1 2001 2023  350.8549
1 1 2001 2024 71.105095
1 1 2001 2016 323.36795
2 5 2009 2009  555.1032
2 5 2009 2010   875.991
2 5 2009 2011 204.70947
2 5 2009 2012  892.7587
3 1 1998 2003  584.4658
3 1 1998 2001  369.7791
3 1 1998 2004  850.6309
4 7 2003 2009  391.3819
4 7 2003 2010 119.66132
4 7 2003 2011  754.2434
4 7 2003 2012  695.0234
5 5 2007 2011  686.6152
5 5 2007 2013  931.9346
5 5 2007 2015  454.8882
end

drop expense 

bysort parents_id id (year) : gen tag = _n == 1 

egen birth_order = rank(birth_year) if tag, by(parents_id)

bysort parents_id id (birth_order) : replace birth_order = birth_order[_n-1] if missing(birth_order) 

sort parents_id birth_year id 

list, sepby(parents_id birth_year id) 

     +--------------------------------------------------+
     | id   parent~d   birth~ar   year   tag   birth~er |
     |--------------------------------------------------|
  1. |  3          1       1998   2003     0          1 |
  2. |  3          1       1998   2001     1          1 |
  3. |  3          1       1998   2004     0          1 |
     |--------------------------------------------------|
  4. |  1          1       2001   2021     0          2 |
  5. |  1          1       2001   2019     0          2 |
  6. |  1          1       2001   2022     0          2 |
  7. |  1          1       2001   2023     0          2 |
  8. |  1          1       2001   2020     0          2 |
  9. |  1          1       2001   2016     1          2 |
 10. |  1          1       2001   2024     0          2 |
 11. |  1          1       2001   2018     0          2 |
     |--------------------------------------------------|
 12. |  5          5       2007   2011     1          1 |
 13. |  5          5       2007   2013     0          1 |
 14. |  5          5       2007   2015     0          1 |
     |--------------------------------------------------|
 15. |  2          5       2009   2009     1          2 |
 16. |  2          5       2009   2011     0          2 |
 17. |  2          5       2009   2012     0          2 |
 18. |  2          5       2009   2010     0          2 |
     |--------------------------------------------------|
 19. |  4          7       2003   2010     0          1 |
 20. |  4          7       2003   2011     0          1 |
 21. |  4          7       2003   2012     0          1 |
 22. |  4          7       2003   2009     1          1 |
     +--------------------------------------------------+

[Nicole Kashani]

Here is some technique.

What can be done:

Use each individual just once in assigning birth order

What is harder:

What to do with twins and other multiple births in the same year.

What to do with twins and other multiple births in two years (first birth say on Dec 31, last birth on 1 Jan).

What to do when siblings are born in the same year but from different pregnancies (say one in Jan, another in December of same year).

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id parents_id birth_year year expense)
1 1 2001 2020 348.8717
1 1 2001 2019 266.8857
1 1 2001 2018 136.6463
1 1 2001 2021 28.556866
1 1 2001 2022 868.9333
1 1 2001 2023 350.8549
1 1 2001 2024 71.105095
1 1 2001 2016 323.36795
2 5 2009 2009 555.1032
2 5 2009 2010 875.991
2 5 2009 2011 204.70947
2 5 2009 2012 892.7587
3 1 1998 2003 584.4658
3 1 1998 2001 369.7791
3 1 1998 2004 850.6309
4 7 2003 2009 391.3819
4 7 2003 2010 119.66132
4 7 2003 2011 754.2434
4 7 2003 2012 695.0234
5 5 2007 2011 686.6152
5 5 2007 2013 931.9346
5 5 2007 2015 454.8882
end

drop expense

bysort parents_id id (year) : gen tag = _n == 1

egen birth_order = rank(birth_year) if tag, by(parents_id)

bysort parents_id id (birth_order) : replace birth_order = birth_order[_n-1] if missing(birth_order)

sort parents_id birth_year id

list, sepby(parents_id birth_year id)

+--------------------------------------------------+
| id parent~d birth~ar year tag birth~er |
|--------------------------------------------------|
1. | 3 1 1998 2003 0 1 |
2. | 3 1 1998 2001 1 1 |
3. | 3 1 1998 2004 0 1 |
|--------------------------------------------------|
4. | 1 1 2001 2021 0 2 |
5. | 1 1 2001 2019 0 2 |
6. | 1 1 2001 2022 0 2 |
7. | 1 1 2001 2023 0 2 |
8. | 1 1 2001 2020 0 2 |
9. | 1 1 2001 2016 1 2 |
10. | 1 1 2001 2024 0 2 |
11. | 1 1 2001 2018 0 2 |
|--------------------------------------------------|
12. | 5 5 2007 2011 1 1 |
13. | 5 5 2007 2013 0 1 |
14. | 5 5 2007 2015 0 1 |
|--------------------------------------------------|
15. | 2 5 2009 2009 1 2 |
16. | 2 5 2009 2011 0 2 |
17. | 2 5 2009 2012 0 2 |
18. | 2 5 2009 2010 0 2 |
|--------------------------------------------------|
19. | 4 7 2003 2010 0 1 |
20. | 4 7 2003 2011 0 1 |
21. | 4 7 2003 2012 0 1 |
22. | 4 7 2003 2009 1 1 |
+--------------------------------------------------+

I found out I have data on the day of birth and month of birth. Sorry for the oversight. How would that change the code you sent to deal with the twins?

[Nick Cox]

Create a birth daily date variable and use it instead of birth year.