Create a new count variable within group sorted by year

[Micah Nelson]

Hello,

I'm working with some panel data that looks similar to this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str63 state float(year X)
"NY" 2000 0
"NY" 2001 1
"NY" 2002 1
"NY" 2003 0
"NY" 2004 1
"NY" 2005 1
"NC" 2000 1
"NC" 2001 1
"NC" 2002 1
"NC" 2003 1
"NC" 2004 1
"NC" 2005 0
end

I'd like to create a new variable that counts the number of rows in which X == 1 sequentially within states, chronologically by year. That would look like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str63 state float(year X count)
"NY" 2000 0 0
"NY" 2001 1 1
"NY" 2002 1 2
"NY" 2003 0 0
"NY" 2004 1 1
"NY" 2005 1 2
"NC" 2000 1 1
"NC" 2001 1 2
"NC" 2002 1 3
"NC" 2003 1 4
"NC" 2004 1 5
"NC" 2005 0 0
end

But I can't figure out how to achieve this. I've tried

bysort state X: gen count = _n

and that ends up counting sequentially when X == 1 within each state, but not chronologically by year.

How would I go about creating this new variable?
Thanks for your help.

[Ken Chui]

It seems they are spell countings. See these resources:

https://journals.sagepub.com/doi/pdf...867X0700700209

https://www.stata.com/support/faqs/d...-observations/

encode state, gen(nstate)

* tsspell needs to be installed for the first time:
ssc install tsspell

tsset nstate year
tsspell X if X == 1

gsort -state year

list, sep(0)

Results are below. The variable _seq is the one you want:

     +--------------------------------------------------+
     | state   year   X   nstate   _spell   _seq   _end |
     |--------------------------------------------------|
  1. |    NY   2000   0       NY        0      0      0 |
  2. |    NY   2001   1       NY        1      1      0 |
  3. |    NY   2002   1       NY        1      2      1 |
  4. |    NY   2003   0       NY        0      0      0 |
  5. |    NY   2004   1       NY        2      1      0 |
  6. |    NY   2005   1       NY        2      2      1 |
  7. |    NC   2000   1       NC        1      1      0 |
  8. |    NC   2001   1       NC        1      2      0 |
  9. |    NC   2002   1       NC        1      3      0 |
 10. |    NC   2003   1       NC        1      4      0 |
 11. |    NC   2004   1       NC        1      5      1 |
 12. |    NC   2005   0       NC        0      0      0 |
     +--------------------------------------------------+

[Micah Nelson]

Hey that worked! Thank you!

[Nick Cox]

tsspell, cond(X == 1)

is equivalent, given a tsset.