Assign _n within group for spell

[HeeJu Jang-Paulsen]

Dear Statalist,

I would really appreciate if you could help me on this as I've been trying to solve this problem for hours now!
Below is what my data looks like (copied via dataex).

clear
input long(id casenum) byte(active startmonth) int startyear
1 1 0 1 2017
1 1 0 2 2017
1 1 1 3 2017
1 1 0 4 2017
1 1 0 5 2017
1 1 0 6 2017
1 1 0 7 2017
1 1 0 8 2017
1 1 0 9 2017
1 1 0 10 2017
1 1 0 11 2017
1 1 0 12 2017
1 1 0 1 2018
1 1 0 2 2018
1 1 1 3 2018
1 1 1 4 2018
1 1 1 5 2018
1 1 1 6 2018
1 1 1 7 2018
1 1 1 8 2018
1 1 1 9 2018
1 1 1 10 2018
1 1 1 11 2018
1 1 1 12 2018
1 1 1 1 2019
1 1 1 2 2019
1 1 1 3 2019
1 1 1 4 2019
1 1 1 5 2019
1 1 1 6 2019
1 1 0 7 2019
1 1 0 8 2019
1 1 0 9 2019
1 1 0 10 2019
1 1 0 11 2019
1 1 0 12 2019
1 1 0 1 2020
1 1 0 2 2020
1 1 0 3 2020
1 1 0 4 2020
1 1 0 5 2020
1 1 0 6 2020
1 1 0 7 2020
1 1 0 8 2020
1 1 0 9 2020
1 1 1 10 2020
1 1 1 11 2020
1 1 1 12 2020
1 1 1 1 2021
1 1 1 2 2021
1 1 1 3 2021
1 1 1 4 2021
1 1 1 5 2021
1 1 1 6 2021
1 1 1 7 2021
1 2 0 1 2017
1 2 0 2 2017
1 2 0 3 2017
1 2 0 4 2017
1 2 0 5 2017
1 2 0 6 2017
1 2 0 7 2017
1 2 0 8 2017
1 2 0 9 2017
1 2 0 10 2017
1 2 0 11 2017
1 2 0 12 2017
1 2 0 1 2018
1 2 0 2 2018
1 2 0 3 2018
1 2 0 4 2018
1 2 0 5 2018
1 2 0 6 2018
1 2 0 7 2018
1 2 0 8 2018
1 2 0 9 2018
1 2 0 10 2018
1 2 0 11 2018
1 2 0 12 2018
1 2 1 1 2019
1 2 1 2 2019
1 2 1 3 2019
1 2 1 4 2019
1 2 1 5 2019
1 2 1 6 2019
1 2 0 7 2019
1 2 0 8 2019
1 2 0 9 2019
1 2 0 10 2019
1 2 0 11 2019
1 2 0 12 2019
1 2 0 1 2020
1 2 0 2 2020
1 2 0 3 2020
1 2 0 4 2020
1 2 0 5 2020
1 2 0 6 2020
1 2 0 7 2020
1 2 0 8 2020
1 2 0 9 2020
end


As you can see in this example, there are two different case numbers (1 and 2) for an individual ID = 1.
I've sorted the data with "sort id casenum startyear startmonth active".

I want to assign _n for every time 'active' changes from 1 to 0 or vice versa within the unique id and case number.
Put differently, for ID =1 & casenum =1, I want to create a new variable (let's call it 'spell') that will equal 1 for the first two rows (active = 0), 2 for the third row (active = 1), 3 for rows 4 to 14, and so on.

Hope this makes sense! Please feel free to ask for any clarification & I really appreciate your help in advance!

[Nick Cox]

Thanks for the data example. This uses tsspell from SSC. Download tsspell for implementation, but see https://www.stata-journal.com/articl...article=dm0029 for principles. (Small story: the paper was long enough, and late enough, without details of that implementation being added. A sequel would just rehash the help file for tsspell, so I never wrote it.)

clear
input long(id casenum) byte(active startmonth) int startyear
1 1 0 1 2017
1 1 0 2 2017
1 1 1 3 2017
1 1 0 4 2017
1 1 0 5 2017
1 1 0 6 2017
1 1 0 7 2017
1 1 0 8 2017
1 1 0 9 2017
1 1 0 10 2017
1 1 0 11 2017
1 1 0 12 2017
1 1 0 1 2018
1 1 0 2 2018
1 1 1 3 2018
1 1 1 4 2018
1 1 1 5 2018
1 1 1 6 2018
1 1 1 7 2018
1 1 1 8 2018
1 1 1 9 2018
1 1 1 10 2018
1 1 1 11 2018
1 1 1 12 2018
1 1 1 1 2019
1 1 1 2 2019
1 1 1 3 2019
1 1 1 4 2019
1 1 1 5 2019
1 1 1 6 2019
1 1 0 7 2019
1 1 0 8 2019
1 1 0 9 2019
1 1 0 10 2019
1 1 0 11 2019
1 1 0 12 2019
1 1 0 1 2020
1 1 0 2 2020
1 1 0 3 2020
1 1 0 4 2020
1 1 0 5 2020
1 1 0 6 2020
1 1 0 7 2020
1 1 0 8 2020
1 1 0 9 2020
1 1 1 10 2020
1 1 1 11 2020
1 1 1 12 2020
1 1 1 1 2021
1 1 1 2 2021
1 1 1 3 2021
1 1 1 4 2021
1 1 1 5 2021
1 1 1 6 2021
1 1 1 7 2021
1 2 0 1 2017
1 2 0 2 2017
1 2 0 3 2017
1 2 0 4 2017
1 2 0 5 2017
1 2 0 6 2017
1 2 0 7 2017
1 2 0 8 2017
1 2 0 9 2017
1 2 0 10 2017
1 2 0 11 2017
1 2 0 12 2017
1 2 0 1 2018
1 2 0 2 2018
1 2 0 3 2018
1 2 0 4 2018
1 2 0 5 2018
1 2 0 6 2018
1 2 0 7 2018
1 2 0 8 2018
1 2 0 9 2018
1 2 0 10 2018
1 2 0 11 2018
1 2 0 12 2018
1 2 1 1 2019
1 2 1 2 2019
1 2 1 3 2019
1 2 1 4 2019
1 2 1 5 2019
1 2 1 6 2019
1 2 0 7 2019
1 2 0 8 2019
1 2 0 9 2019
1 2 0 10 2019
1 2 0 11 2019
1 2 0 12 2019
1 2 0 1 2020
1 2 0 2 2020
1 2 0 3 2020
1 2 0 4 2020
1 2 0 5 2020
1 2 0 6 2020
1 2 0 7 2020
1 2 0 8 2020
1 2 0 9 2020
end

gen mdate = ym(startyear, startmonth)
format mdate %tm 
egen case_id = group(id casenum), label 
tsset case_id mdate 

tsspell active 

list case_id mdate active _* , sepby(case_id _spell) 

     +---------------------------------------------------+
     | case_id     mdate   active   _spell   _seq   _end |
     |---------------------------------------------------|
  1. |     1 1    2017m1        0        1      1      0 |
  2. |     1 1    2017m2        0        1      2      1 |
     |---------------------------------------------------|
  3. |     1 1    2017m3        1        2      1      1 |
     |---------------------------------------------------|
  4. |     1 1    2017m4        0        3      1      0 |
  5. |     1 1    2017m5        0        3      2      0 |
  6. |     1 1    2017m6        0        3      3      0 |
  7. |     1 1    2017m7        0        3      4      0 |
  8. |     1 1    2017m8        0        3      5      0 |
  9. |     1 1    2017m9        0        3      6      0 |
 10. |     1 1   2017m10        0        3      7      0 |
 11. |     1 1   2017m11        0        3      8      0 |
 12. |     1 1   2017m12        0        3      9      0 |
 13. |     1 1    2018m1        0        3     10      0 |
 14. |     1 1    2018m2        0        3     11      1 |
     |---------------------------------------------------|
 15. |     1 1    2018m3        1        4      1      0 |
 16. |     1 1    2018m4        1        4      2      0 |
 17. |     1 1    2018m5        1        4      3      0 |
 18. |     1 1    2018m6        1        4      4      0 |
 19. |     1 1    2018m7        1        4      5      0 |
 20. |     1 1    2018m8        1        4      6      0 |
 21. |     1 1    2018m9        1        4      7      0 |
 22. |     1 1   2018m10        1        4      8      0 |
 23. |     1 1   2018m11        1        4      9      0 |
 24. |     1 1   2018m12        1        4     10      0 |
 25. |     1 1    2019m1        1        4     11      0 |
 26. |     1 1    2019m2        1        4     12      0 |
 27. |     1 1    2019m3        1        4     13      0 |
 28. |     1 1    2019m4        1        4     14      0 |
 29. |     1 1    2019m5        1        4     15      0 |
 30. |     1 1    2019m6        1        4     16      1 |
     |---------------------------------------------------|
 31. |     1 1    2019m7        0        5      1      0 |
 32. |     1 1    2019m8        0        5      2      0 |
 33. |     1 1    2019m9        0        5      3      0 |
 34. |     1 1   2019m10        0        5      4      0 |
 35. |     1 1   2019m11        0        5      5      0 |
 36. |     1 1   2019m12        0        5      6      0 |
 37. |     1 1    2020m1        0        5      7      0 |
 38. |     1 1    2020m2        0        5      8      0 |
 39. |     1 1    2020m3        0        5      9      0 |
 40. |     1 1    2020m4        0        5     10      0 |
 41. |     1 1    2020m5        0        5     11      0 |
 42. |     1 1    2020m6        0        5     12      0 |
 43. |     1 1    2020m7        0        5     13      0 |
 44. |     1 1    2020m8        0        5     14      0 |
 45. |     1 1    2020m9        0        5     15      1 |
     |---------------------------------------------------|
 46. |     1 1   2020m10        1        6      1      0 |
 47. |     1 1   2020m11        1        6      2      0 |
 48. |     1 1   2020m12        1        6      3      0 |
 49. |     1 1    2021m1        1        6      4      0 |
 50. |     1 1    2021m2        1        6      5      0 |
 51. |     1 1    2021m3        1        6      6      0 |
 52. |     1 1    2021m4        1        6      7      0 |
 53. |     1 1    2021m5        1        6      8      0 |
 54. |     1 1    2021m6        1        6      9      0 |
 55. |     1 1    2021m7        1        6     10      1 |
     |---------------------------------------------------|
 56. |     1 2    2017m1        0        1      1      0 |
 57. |     1 2    2017m2        0        1      2      0 |
 58. |     1 2    2017m3        0        1      3      0 |
 59. |     1 2    2017m4        0        1      4      0 |
 60. |     1 2    2017m5        0        1      5      0 |
 61. |     1 2    2017m6        0        1      6      0 |
 62. |     1 2    2017m7        0        1      7      0 |
 63. |     1 2    2017m8        0        1      8      0 |
 64. |     1 2    2017m9        0        1      9      0 |
 65. |     1 2   2017m10        0        1     10      0 |
 66. |     1 2   2017m11        0        1     11      0 |
 67. |     1 2   2017m12        0        1     12      0 |
 68. |     1 2    2018m1        0        1     13      0 |
 69. |     1 2    2018m2        0        1     14      0 |
 70. |     1 2    2018m3        0        1     15      0 |
 71. |     1 2    2018m4        0        1     16      0 |
 72. |     1 2    2018m5        0        1     17      0 |
 73. |     1 2    2018m6        0        1     18      0 |
 74. |     1 2    2018m7        0        1     19      0 |
 75. |     1 2    2018m8        0        1     20      0 |
 76. |     1 2    2018m9        0        1     21      0 |
 77. |     1 2   2018m10        0        1     22      0 |
 78. |     1 2   2018m11        0        1     23      0 |
 79. |     1 2   2018m12        0        1     24      1 |
     |---------------------------------------------------|
 80. |     1 2    2019m1        1        2      1      0 |
 81. |     1 2    2019m2        1        2      2      0 |
 82. |     1 2    2019m3        1        2      3      0 |
 83. |     1 2    2019m4        1        2      4      0 |
 84. |     1 2    2019m5        1        2      5      0 |
 85. |     1 2    2019m6        1        2      6      1 |
     |---------------------------------------------------|
 86. |     1 2    2019m7        0        3      1      0 |
 87. |     1 2    2019m8        0        3      2      0 |
 88. |     1 2    2019m9        0        3      3      0 |
 89. |     1 2   2019m10        0        3      4      0 |
 90. |     1 2   2019m11        0        3      5      0 |
 91. |     1 2   2019m12        0        3      6      0 |
 92. |     1 2    2020m1        0        3      7      0 |
 93. |     1 2    2020m2        0        3      8      0 |
 94. |     1 2    2020m3        0        3      9      0 |
 95. |     1 2    2020m4        0        3     10      0 |
 96. |     1 2    2020m5        0        3     11      0 |
 97. |     1 2    2020m6        0        3     12      0 |
 98. |     1 2    2020m7        0        3     13      0 |
 99. |     1 2    2020m8        0        3     14      0 |
100. |     1 2    2020m9        0        3     15      1 |
     +---------------------------------------------------+

[HeeJu Jang-Paulsen]

Thank you so much Nick!! This is exactly what I was hoping to do!