Calcluating sex ratio at higher birth order conditional on first birth order

[Kanika Dua]

Hello all,

I am working on a dataset that has the birth histories of several women across years. I want to calculate the sex ratio at birth for birth order 2, conditional on the fact that a female was born at birth order 1. I would like to request a code on how to go about doing this. My dataset looks as follows

* Example generated by -dataex-. For more info, type help dataex
clear
input str15 caseid byte bord int b2 byte b4
"  0100101345 02" 2 2011 1
"  0100101345 02" 1 2005 1
"  0100101383 02" 3 2003 2
"  0100101383 02" 2 1999 1
"  0100101383 02" 1 1996 2
"  0100101334 02" 3 2013 1
"  0100101334 02" 2 2011 2
"  0100101334 02" 1 2008 1
"  0100101301 04" 2 2011 2
"  0100101301 04" 1 2007 2
"  0100101399 02" 3 2015 1
"  0100101399 02" 2 2011 2
"  0100101399 02" 1 2009 2
"  0100101375 02" 2 2004 1
"  0100101375 02" 1 2000 2
"  0100101337 02" 2 2009 1
"  0100101337 02" 1 2007 2
"  0100101335 02" 4 2010 1
"  0100101335 02" 3 2005 2
"  0100101335 02" 2 2000 1
"  0100101335 02" 1 1996 1
"  0100101357 02" 4 2018 2
"  0100101357 02" 3 2013 2
"  0100101357 02" 2 2010 1
"  0100101357 02" 1 2006 2
"  0100101395 02" 1 2017 2
"  0100101316 02" 3 2000 1
"  0100101316 02" 2 1996 2
"  0100101316 02" 1 1993 1
"  0100101358 04" 1 2018 1
"  0100101322 02" 3 2009 1
"  0100101322 02" 2 2006 1
"  0100101322 02" 1 2003 1
"  0100101380 02" 2 2016 1
"  0100101380 02" 1 2014 2
"  0100101362 02" 3 2005 1
"  0100101362 02" 2 2004 2
"  0100101362 02" 1 2000 1
"  0100101321 02" 3 2000 1
"  0100101321 02" 2 1999 1
"  0100101321 02" 1 1995 2
"  0100101393 02" 3 2012 1
"  0100101393 02" 2 2009 2
"  0100101393 02" 1 2007 2
"  0100103091 04" 1 2012 1
"  0100103016 03" 3 2011 1
"  0100103016 03" 2 2008 1
"  0100103016 03" 1 2005 1
"  0100103057 02" 2 2003 2
"  0100103057 02" 1 2001 1
"  0100103055 04" 2 2007 1
"  0100103055 04" 1 2002 1
"  0100103022 04" 1 2015 1
"  0100103068 04" 2 2013 2
"  0100103068 04" 1 2010 1
"  0100103063 02" 3 2005 1
"  0100103063 02" 2 2003 1
"  0100103063 02" 1 2000 1
"  0100103098 02" 1 2019 2
"  0100103026 02" 3 2009 2
"  0100103026 02" 2 2006 2
"  0100103026 02" 1 2004 2
"  0100103079 02" 2 2009 2
"  0100103079 02" 1 2007 1
"  0100103033 02" 4 2014 2
"  0100103033 02" 3 2012 1
"  0100103033 02" 2 2011 1
"  0100103033 02" 1 2009 1
"  0100103085 02" 3 2018 1
"  0100103085 02" 2 2001 2
"  0100103085 02" 1 1998 2
"  0100103089 02" 3 2011 2
"  0100103089 02" 2 2008 1
"  0100103089 02" 1 2006 1
"  0100103020 02" 1 2019 2
"  0100103047 02" 3 1999 1
"  0100103047 02" 2 1997 2
"  0100103047 02" 1 1995 1
"  0100100678 02" 4 2011 1
"  0100100678 02" 3 2008 2
"  0100100678 02" 2 2006 1
"  0100100678 02" 1 2004 2
"  0100100686 02" 3 2011 1
"  0100100686 02" 2 2007 2
"  0100100686 02" 1 2004 2
"  0100100643 04" 3 2011 2
"  0100100643 04" 2 2007 1
"  0100100643 04" 1 2004 2
"  0100100618 04" 2 2016 2
"  0100100618 04" 1 2013 1
"  0100100650 02" 4 2016 2
"  0100100650 02" 3 2014 2
"  0100100650 02" 2 2012 1
"  0100100650 02" 1 2010 1
"  0100100661 02" 1 2004 2
"  0100100616 04" 1 2019 1
"  0100100602 02" 2 2009 2
"  0100100602 02" 1 2007 2
"  0100100693 02" 4 2011 1
"  0100100693 02" 3 2008 1
end
label values b4 B4
label def B4 1 "male", modify
label def B4 2 "female", modify

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

Caseid identifies each woman uniquely. Bord is the birth order. b2 is the year of the birth. b4 is the sex of the birth.

Thank you very much in advance!

Regards,
Kanika

[Clyde Schechter]

by caseid (bord), sort: egen sex_first_child = max(cond(bord == 1, b4, .))
by caseid (bord): egen sex_second_child = max(cond(bord == 2, b4, .))
label values sex*child B4
gen byte second_child_male = (sex_second_child == "male":B4) ///
    if !missing(sex_second_child)
egen case_tag = tag(caseid)
summ second_child_male if case_tag & sex_first_child == "female":B4, meanonly
display "Sex ratio = " %3.2f =`r(mean)'/(1-`r(mean)')

[Kanika Dua]

Thank you so so much Clyde! It is what I needed.