Creating a variable based on another dummy variable

[Hakan Gunduz]

Hi,

I want to create a variable that tells for how many consecutive years the variable zerolev is equal to 1 before it turns 0 again. Here's an example:

fyear    zerolev
1991    0
1992    0
1993    1
1994    0
1995    0
1996    1
1997    1
1998    0
1999    0
2000    0
2001    1
2002    1
2003    1
2004    0
2005    0
2006    0
2007    0
2008    0
2009    0
2010    0

The variable I want to create should look like this:

fyear    zerolev    newvar
1991    0    0
1992    0    0
1993    1    1
1994    0    0
1995    0    0
1996    1    0
1997    1    2
1998    0    0
1999    0    0
2000    0    0
2001    1    0
2002    1    0
2003    1    3
2004    0    0
2005    0    0
2006    0    0
2007    0    0
2008    0    0
2009    0    0
2010    0    0

How can I create such variable? Many thanks.

[Nick Cox]

This can be approached using the idea of spells. You are interested in spells in which zerolev is 1 and wish to mark the last observation in each with its counter.

See https://www.stata-journal.com/articl...article=dm0029 or the command tsspell from SSC.

For a very recent example of spells, see https://www.statalist.org/forums/for...ate-start_date

Here are two ways to do it.

clear 
input fyear    zerolev
1991    0
1992    0
1993    1
1994    0
1995    0
1996    1
1997    1
1998    0
1999    0
2000    0
2001    1
2002    1
2003    1
2004    0
2005    0
2006    0
2007    0
2008    0
2009    0
2010    0
end 

gen wanted = 0
replace wanted = wanted[_n-1] + 1 if zerolev == 1
replace wanted = 0 if wanted[_n+1] > 0  

tsset fyear 
tsspell, cond(zerolev == 1)
gen WANTED = _end * _seq

assert wanted == WANTED 

list, sepby(zerolev) 

     +----------------------------------------------------------+
     | fyear   zerolev   wanted   _seq   _spell   _end   WANTED |
     |----------------------------------------------------------|
  1. |  1991         0        0      0        0      0        0 |
  2. |  1992         0        0      0        0      0        0 |
     |----------------------------------------------------------|
  3. |  1993         1        1      1        1      1        1 |
     |----------------------------------------------------------|
  4. |  1994         0        0      0        0      0        0 |
  5. |  1995         0        0      0        0      0        0 |
     |----------------------------------------------------------|
  6. |  1996         1        0      1        2      0        0 |
  7. |  1997         1        2      2        2      1        2 |
     |----------------------------------------------------------|
  8. |  1998         0        0      0        0      0        0 |
  9. |  1999         0        0      0        0      0        0 |
 10. |  2000         0        0      0        0      0        0 |
     |----------------------------------------------------------|
 11. |  2001         1        0      1        3      0        0 |
 12. |  2002         1        0      2        3      0        0 |
 13. |  2003         1        3      3        3      1        3 |
     |----------------------------------------------------------|
 14. |  2004         0        0      0        0      0        0 |
 15. |  2005         0        0      0        0      0        0 |
 16. |  2006         0        0      0        0      0        0 |
 17. |  2007         0        0      0        0      0        0 |
 18. |  2008         0        0      0        0      0        0 |
 19. |  2009         0        0      0        0      0        0 |
 20. |  2010         0        0      0        0      0        0 |
     +----------------------------------------------------------+

If the next question is going to be, Thanks, but the real problem is with panel data and that doesn't work, then let's back up and have an identifier too:

drop wanted WANTED _*
     
gen id = 1     

gen wanted = 0
bysort id (fyear): replace wanted = wanted[_n-1] + 1 if zerolev == 1
by id: replace wanted = 0 if wanted[_n+1] > 0  

tsset id fyear 
tsspell, cond(zerolev == 1)
gen WANTED = _end * _seq

assert wanted == WANTED

[Hakan Gunduz]

Thanks a lot for this, Nick.