Length of longest consecutive ones

[Jayanta Talukder]

Hello all,
I'm trying to calculate the longest period of drought in the five years before a census. My data is available by state and by month. I have a binary variable called "drought" that is equal to 1 when SPI (standard precipitation index) values indicate a drought and 0 otherwise. For simplicity, I present a subset of data for 1 state only. The variable longest_drought is what I want to obtain.

state	month	year	drought	longest_drought
1	9	2015	0	6
1	10	2015	0	6
1	11	2015	1	6
1	12	2015	1	6
1	1	2016	1	6
1	2	2016	0	6
1	3	2016	1	6
1	4	2016	0	6
1	5	2016	0	6
1	6	2016	0	6
1	7	2016	0	6
1	8	2016	0	6
1	9	2016	0	6
1	10	2016	1	6
1	11	2016	1	6
1	12	2016	1	6
1	1	2017	1	6
1	2	2017	1	6
1	3	2017	1	6
1	4	2017	0	6

As you can see, the longest drought period is from 10/2016 to 3/2017 for a period of 6 months. I require a variable that is equal to 6 for all the observations listed here.
Thank you.

[Nick Cox]

I don't get what "five years before a census" has to do with your data example.

But see #3 in https://www.statalist.org/forums/for...d-observations (posted earlier today) and consider this:

clear 
input state    month    year    drought    longest_drought
1    9    2015    0    6
1    10    2015    0    6
1    11    2015    1    6
1    12    2015    1    6
1    1    2016    1    6
1    2    2016    0    6
1    3    2016    1    6
1    4    2016    0    6
1    5    2016    0    6
1    6    2016    0    6
1    7    2016    0    6
1    8    2016    0    6
1    9    2016    0    6
1    10    2016    1    6
1    11    2016    1    6
1    12    2016    1    6
1    1    2017    1    6
1    2    2017    1    6
1    3    2017    1    6
1    4    2017    0    6
end 

gen mdate = ym(year, month) 
format mdate %tm 
tsset state mdate 

ssc install tsspell 

tsspell, pcond(drought) 

bysort state (_seq) : gen wanted = _seq[_N] 

sort state mdate 

list mdate drought _* longest wanted, sepby(state _spell) 

     +--------------------------------------------------------------+
     |   mdate   drought   _seq   _spell   _end   longes~t   wanted |
     |--------------------------------------------------------------|
  1. |  2015m9         0      0        0      0          6        6 |
  2. | 2015m10         0      0        0      0          6        6 |
     |--------------------------------------------------------------|
  3. | 2015m11         1      1        1      0          6        6 |
  4. | 2015m12         1      2        1      0          6        6 |
  5. |  2016m1         1      3        1      1          6        6 |
     |--------------------------------------------------------------|
  6. |  2016m2         0      0        0      0          6        6 |
     |--------------------------------------------------------------|
  7. |  2016m3         1      1        2      1          6        6 |
     |--------------------------------------------------------------|
  8. |  2016m4         0      0        0      0          6        6 |
  9. |  2016m5         0      0        0      0          6        6 |
 10. |  2016m6         0      0        0      0          6        6 |
 11. |  2016m7         0      0        0      0          6        6 |
 12. |  2016m8         0      0        0      0          6        6 |
 13. |  2016m9         0      0        0      0          6        6 |
     |--------------------------------------------------------------|
 14. | 2016m10         1      1        3      0          6        6 |
 15. | 2016m11         1      2        3      0          6        6 |
 16. | 2016m12         1      3        3      0          6        6 |
 17. |  2017m1         1      4        3      0          6        6 |
 18. |  2017m2         1      5        3      0          6        6 |
 19. |  2017m3         1      6        3      1          6        6 |
     |--------------------------------------------------------------|
 20. |  2017m4         0      0        0      0          6        6 |
     +--------------------------------------------------------------+

[Jayanta Talukder]

That worked brilliantly. Thank you so much. I'm looking at migration patterns in response to climate variation, such as longest drought, drought magnitude, temp extremes, etc in the 5 years preceding the migration as per each census enumeration..