Dear All, I found this question here (https://bbs.pinggu.org/thread-9071118-1-1.html). The data set is
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id int year byte(cut invest wanted1 wanted2)
1 2000 0 1 0 0
1 2001 0 1 1 1
1 2002 0 1 2 2
1 2003 0 1 3 3
1 2004 0 1 4 4
1 2005 1 1 5 5
1 2006 0 1 1 6
1 2007 0 0 2 7
1 2008 0 1 0 0
1 2009 0 0 1 1
1 2010 0 1 0 0
1 2011 0 1 1 1
1 2012 1 0 2 2
1 2013 0 1 0 0
1 2014 0 1 1 1
1 2015 0 0 2 2
2 2000 0 1 0 0
2 2001 0 1 1 1
2 2002 1 1 2 2
2 2003 0 1 1 3
2 2004 0 1 2 4
2 2005 0 0 3 5
2 2006 1 0 0 0
2 2007 0 0 0 0
2 2008 0 0 0 0
2 2009 0 1 0 0
2 2010 0 0 1 1
2 2011 0 1 0 0
2 2012 1 0 1 1
2 2013 0 0 0 0
2 2014 0 0 0 0
2 2015 0 1 0 0
end
For each `id', I'd like to obtain `wanted1' and `wanted2'. First, if there is loss in that year, `invest' = 1, 0 otherwise. `wanted2' is the number of consecutive years with loss (invest=1) before the current year. I can do this by
Code:
xtset id year
// ssc install tsspell
tsspell invest
bys id _spell: gen a = sum(invest)
sort id year
gen wanted2a = L.a
replace wanted2a = 0 if wanted2a == .
order wanted2a, after(wanted2)
My problem is how to obtain `wanted1'. The key difference between `wanted1' and `wanted2' is that, for `wanted1', the number re-counts after cut=1. Any suggestion is highly appreciated.