Dear Statalisters,

I am trying to calculate the spell length to capture the first transition from public sector employment to any other sector.

My code (below) works well if the person starts in the public sector and stays in the public sector
or
the person starts in the public sector and transition to another sector.

However, the code does not work correctly if:
the person starts with working in the other sectors and stays in other (I need to drop these individuals)
and
either starts in other sectors and moves to public and then stays in public or moves to other. In this case, I need the spell to start when they started working in the public sector.
Please advise me.

Thank you,
Maye

Code:
drop if publicmnl01==. //Dropping the missing observations
*GENERATE VARIABLE THAT IDENTIFIES ANY KIND OF TRANSITION
bysort indid (year): gen anytransition = cond( publicmnl01 [_n] !=publicmnl01[_n-1], 1, 0 )

*GENERATE INDICATOR FOR OBS. FOLLOWING public sector TO other TRANSITION
bysort indid (year): gen PU2other= cond(anytransition==1 & publicmnl01[_n-1]==0 & publicmnl01==1, 1, 0) 

*In order to change the following to 1 
bys indid (year): replace PU2other= PU2other[_n-1] if publicmnl01==1 & anytransition==0 
*in order to calculate the spell, I need to transfer the PU2other to the opposite 
recode PU2other (0=1) (1=0), gen(other2pu)
by indid (year), sort: gen spellpublic = sum(other2pu)

*to include in the count the year of transitioning to other status: 
by indid (year), sort: replace spellpublic = spellpublic[_n-1] +1 if PU2other==1 & PU2other[_n-1]==0

*dropping the observations after the first transition: 
by indid (year), sort: drop if PU2other==1 & PU2other[_n-1]==1

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str23 indid float(year publicmnl01)
"1812010001101" 1982 0
"1812010001101" 1983 0
"1812010001101" 1984 0
"1812010001101" 1985 0
"1812010001101" 1986 0
"1812010001101" 1987 0
"1812010001101" 1988 0
"1812010001101" 1989 0
"1812010001101" 1990 0
"1812010001101" 1991 0
"1812010001101" 1992 1
"1812010001101" 1993 1
"1812010001101" 1994 1
"1812010001101" 1995 1
"1812010001101" 1996 1
"1812010001101" 1997 1
"1812010001101" 1998 1
"1812010001101" 1999 1
"1812010001101" 2000 1
"1812010001101" 2001 1
"1812010001101" 2002 1
"1812010001101" 2003 1
"1812010001101" 2004 1
"1812010001101" 2005 1
"1812010001101" 2006 1
"1812010001101" 2007 1
"1812010001101" 2008 1
"1812010001101" 2009 1
"1812010001101" 2010 1
"1812010001101" 2011 1
"1812010001101" 2012 1
"1812010001101" 2013 1
"1812010001101" 2014 1
"1812010001101" 2015 1
"1812010001101" 2016 1
"1812010001101" 2017 1
"1812010001101" 2018 1
"1812010018102" 2003 1
"1812010018102" 2004 1
"1812010018102" 2005 1
"1812010018102" 2006 1
"1812010018102" 2007 1
"1812010018102" 2008 1
"1812010018102" 2009 1
"1812010018102" 2010 1
"1812010018102" 2011 1
"1812010018102" 2012 1
"1812010018102" 2013 1
"1812010018102" 2014 1
"1812010018102" 2015 1
"1812010018102" 2016 1
"1812010018102" 2017 1
"1812010026102" 2007 1
"1812010026102" 2008 1
"1812010026102" 2009 1
"1812010026102" 2010 1
"1812010026102" 2011 1
"1812010026102" 2012 1
"1812010026102" 2013 1
"1812010026102" 2014 1
"1812010026102" 2015 1
"1812010026102" 2016 1
"1812010026102" 2017 1
"1812010026102" 2018 1
"1812010030102" 2005 1
"1812010030102" 2006 1
"1812010030102" 2007 1
"1812010030102" 2008 1
"1812010030102" 2009 1
"1812010030102" 2010 1
"1812010030102" 2011 1
"1812010030102" 2012 1
"1812010030102" 2013 1
"1812010030102" 2014 1
"1812010030102" 2015 1
"1812010030102" 2016 1
"1812010030102" 2017 1
"1812010030102" 2018 1
"1812010035102" 2003 1
"1812010035102" 2004 1
"1812010035102" 2005 1
"1812010035102" 2006 1
"1812010035102" 2007 1
"1812010035102" 2008 1
"1812010035102" 2009 1
"1812010035102" 2010 1
"1812010035102" 2011 1
"1812010035102" 2012 1
"1812010035102" 2013 1
"1812010035102" 2014 1
"1812010035102" 2015 1
"1812010035102" 2016 1
"1812010035102" 2017 1
"1812010036102" 2005 1
"1812010036102" 2006 1
"1812010036102" 2007 1
"1812010036102" 2008 1
"1812010036102" 2009 1
"1812010036102" 2010 1
"1812010036102" 2011 1
end
label values publicmnl01 publicmnl01
label def publicmnl01 0 "public", modify
label def publicmnl01 1 "other", modify