Generating a Count Variable with a Continuous Sequence

[Jaweriah Abdullah]

Hi I am working with a panel data set for 300 districts from 1959 to 1999 using Stata 13. I have got normal and maximum daily temperature data. I am trying to identify the days on which the maximum temperature of a district exceeded the normal temperature for three on more consecutive days. Specifically, I need to generate a variable which takes the value 1 if the maximum temperature exceeds the normal for three or more consecutive days and 0 otherwise? I have downloaded Speaking Stata: Identifying Spells by Nicholas J Cox but am still unclear how to generate the variable. Any help will be appreciated

[Clyde Schechter]

The answer depends on the layout of your data. Since you provide no example, I will imagine a data set provide you with code that would work for that. Hopefully, it will resemble your actual data enough that you can fix up the code accordingly.

Assumptions: There is a variable identifying districts: district_id. There is a date variable, called date. There is a variable for maximum daily temperature, call it max_d_temp. And there is a normal temperature variable, call it n_temp.

gen byte exceeds = max_d_temp > n_temp if !missing(max_d_temp, n_temp)
by district_id (date), sort: gen spell = sum(exceeds != exceeds[_n-1])
by district_id spell (date), sort: gen spell_length = _N
gen byte wanted = spell_length >= 3 & exceeds == 1

In the future, when you want help with code, always give example data. And be sure to use the -dataex- command for that purpose. (See FAQ #12 if you are not familiar with -dataex-.)

[Jaweriah Abdullah]

Thank you very much for your answer. the code works

Sorry for the mistake in posting but here is an example of the data

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(YEAR MONTH DATE DAY LAT LON MAX_TEMP NORMAL_TEMP) long DISTRICT_ID
1977 10  1 274 15.5 76.5 32.42 32.72109 39
1977 10  2 275 15.5 76.5 30.26 32.72109 39
1977 10  3 276 15.5 76.5 28.79 32.72109 39
1977 10  4 277 15.5 76.5 29.35 32.72109 39
1977 10  5 278 15.5 76.5 30.37 32.72109 39
1977 10  6 279 15.5 76.5 27.31 32.72109 39
1977 10  7 280 15.5 76.5 30.39 32.72109 39
1977 10  8 281 15.5 76.5 30.68 32.72109 39
1977 10  9 282 15.5 76.5  30.1 32.72109 39
1977 10 10 283 15.5 76.5 28.93 32.72109 39
1977 10 11 284 15.5 76.5 30.61 32.72109 39
1977 10 12 285 15.5 76.5 31.34 32.72109 39
1977 10 13 286 15.5 76.5 31.58 32.72109 39
1977 10 14 287 15.5 76.5 31.92 32.72109 39
1977 10 15 288 15.5 76.5 31.99 32.72109 39
1977 10 16 289 15.5 76.5 31.76 32.72109 39
1977 10 17 290 15.5 76.5 31.76 32.72109 39
1977 10 18 291 15.5 76.5 30.54 32.72109 39
1977 10 19 292 15.5 76.5 29.54 32.72109 39
1977 10 20 293 15.5 76.5 29.51 32.72109 39
1977 10 21 294 15.5 76.5 30.53 32.72109 39
1977 10 22 295 15.5 76.5 30.48 32.72109 39
1977 10 23 296 15.5 76.5 29.99 32.72109 39
1977 10 24 297 15.5 76.5 30.38 32.72109 39
1977 10 25 298 15.5 76.5 30.16 32.72109 39
1977 10 26 299 15.5 76.5 30.48 32.72109 39
1977 10 27 300 15.5 76.5 31.35 32.72109 39
1977 10 28 301 15.5 76.5 32.31 32.72109 39
1977 10 29 302 15.5 76.5 32.81 32.72109 39
1977 10 30 303 15.5 76.5 33.15 32.72109 39
1977 10 31 304 15.5 76.5 32.59 32.72109 39
1977 11  1 305 15.5 76.5 31.37 32.72109 39
1977 11  2 306 15.5 76.5 29.15 32.72109 39
1977 11  3 307 15.5 76.5 30.32 32.72109 39
1977 11  4 308 15.5 76.5 31.52 32.72109 39
1977 11  5 309 15.5 76.5 31.98 32.72109 39
1977 11  6 310 15.5 76.5 31.37 32.72109 39
1977 11  7 311 15.5 76.5 31.47 32.72109 39
1977 11  8 312 15.5 76.5 31.11 32.72109 39
1977 11  9 313 15.5 76.5 31.07 32.72109 39
1977 11 10 314 15.5 76.5  30.7 32.72109 39
1977 11 11 315 15.5 76.5 30.24 32.72109 39
1977 11 12 316 15.5 76.5 30.48 32.72109 39
1977 11 13 317 15.5 76.5 28.86 32.72109 39
1977 11 14 318 15.5 76.5 27.79 32.72109 39
1977 11 15 319 15.5 76.5 29.41 32.72109 39
1977 11 16 320 15.5 76.5 29.77 32.72109 39
1977 11 17 321 15.5 76.5 29.05 32.72109 39
1977 11 18 322 15.5 76.5 29.04 32.72109 39
1977 11 19 323 15.5 76.5 29.36 32.72109 39
1977 11 20 324 15.5 76.5 30.09 32.72109 39
1977 11 21 325 15.5 76.5  31.3 32.72109 39
1977 11 22 326 15.5 76.5 30.24 32.72109 39
1977 11 23 327 15.5 76.5 28.44 32.72109 39
1977 11 24 328 15.5 76.5 29.78 32.72109 39
1977 11 25 329 15.5 76.5 29.53 32.72109 39
1977 11 26 330 15.5 76.5 29.39 32.72109 39
1977 11 27 331 15.5 76.5 28.69 32.72109 39
1977 11 28 332 15.5 76.5 28.35 32.72109 39
1977 11 29 333 15.5 76.5  29.4 32.72109 39
1977 11 30 334 15.5 76.5  29.6 32.72109 39
1977 12  1 335 15.5 76.5 30.19 32.72109 39
1977 12  2 336 15.5 76.5 30.48 32.72109 39
1977 12  3 337 15.5 76.5 30.57 32.72109 39
1977 12  4 338 15.5 76.5 30.35 32.72109 39
1977 12  5 339 15.5 76.5 29.41 32.72109 39
1977 12  6 340 15.5 76.5 29.53 32.72109 39
1977 12  7 341 15.5 76.5  29.5 32.72109 39
1977 12  8 342 15.5 76.5  29.4 32.72109 39
1977 12  9 343 15.5 76.5 28.44 32.72109 39
1977 12 10 344 15.5 76.5  28.5 32.72109 39
1977 12 11 345 15.5 76.5 28.47 32.72109 39
1977 12 12 346 15.5 76.5 28.44 32.72109 39
1977 12 13 347 15.5 76.5 28.58 32.72109 39
1977 12 14 348 15.5 76.5 28.39 32.72109 39
1977 12 15 349 15.5 76.5 28.18 32.72109 39
1977 12 16 350 15.5 76.5 28.08 32.72109 39
1977 12 17 351 15.5 76.5 27.43 32.72109 39
1977 12 18 352 15.5 76.5  27.8 32.72109 39
1977 12 19 353 15.5 76.5 27.92 32.72109 39
1977 12 20 354 15.5 76.5 28.09 32.72109 39
1977 12 21 355 15.5 76.5 27.93 32.72109 39
1977 12 22 356 15.5 76.5 28.07 32.72109 39
1977 12 23 357 15.5 76.5 28.83 32.72109 39
1977 12 24 358 15.5 76.5  29.2 32.72109 39
1977 12 25 359 15.5 76.5 28.83 32.72109 39
1977 12 26 360 15.5 76.5 28.23 32.72109 39
1977 12 27 361 15.5 76.5 28.94 32.72109 39
1977 12 28 362 15.5 76.5  30.1 32.72109 39
1977 12 29 363 15.5 76.5 29.62 32.72109 39
1977 12 30 364 15.5 76.5 28.05 32.72109 39
1977 12 31 365 15.5 76.5 28.61 32.72109 39
1978  1  1   1 15.5 76.5 30.08 32.72109 39
1978  1  2   2 15.5 76.5 30.63 32.72109 39
1978  1  3   3 15.5 76.5  30.7 32.72109 39
1978  1  4   4 15.5 76.5  30.6 32.72109 39
1978  1  5   5 15.5 76.5 30.27 32.72109 39
1978  1  6   6 15.5 76.5 30.25 32.72109 39
1978  1  7   7 15.5 76.5 29.89 32.72109 39
1978  1  8   8 15.5 76.5 30.22 32.72109 39
end

I use the code that you specified as follows

gen TEMP_DIFF= NORMAL_TEMP - MAX_TEMP
gen byte exceeds =1 if TEMP_DIFF>3
replace exceeds =1 if TEMP_DIFF==3
replace exceeds =0 if exceeds==.
by DISTRICT_ID (YEAR MONTH DATE), sort: gen spell = sum(exceeds != exceeds[_n-1])
by DISTRICT_ID spell (YEAR MONTH DATE), sort: gen spell_length = _N
gen byte wanted = spell_length >= 3 & exceeds == 1

this generates the variable that takes the value 1 if max temp exceeds the normal temp by 3 or more degrees for 3 or more days consecutively (for a given district) .

Once again thank you very much

[Nick Cox]

Clyde gave excellent advice as always. Here is a footnote with some extra small points.

First, you will sooner or later find -- as implied by Clyde too -- that it's a lot easier to work with a single daily date variable in Stata.

Second, here is how to implement the approach discussed in the reference you alluded to in #1. I here use tsspell from SSC.

Note that your TEMP_DIFF variable picks up relatively cold spells, not relatively warm ones! In #1 you said you were interested in
maximum temperature of a district exceeding the normal temperature, but in #3 you're looking for big differences the other way.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(YEAR MONTH DATE DAY LAT LON MAX_TEMP NORMAL_TEMP) long DISTRICT_ID
1977 10  1 274 15.5 76.5 32.42 32.72109 39
1977 10  2 275 15.5 76.5 30.26 32.72109 39
1977 10  3 276 15.5 76.5 28.79 32.72109 39
1977 10  4 277 15.5 76.5 29.35 32.72109 39
1977 10  5 278 15.5 76.5 30.37 32.72109 39
1977 10  6 279 15.5 76.5 27.31 32.72109 39
1977 10  7 280 15.5 76.5 30.39 32.72109 39
1977 10  8 281 15.5 76.5 30.68 32.72109 39
1977 10  9 282 15.5 76.5  30.1 32.72109 39
1977 10 10 283 15.5 76.5 28.93 32.72109 39
1977 10 11 284 15.5 76.5 30.61 32.72109 39
1977 10 12 285 15.5 76.5 31.34 32.72109 39
1977 10 13 286 15.5 76.5 31.58 32.72109 39
1977 10 14 287 15.5 76.5 31.92 32.72109 39
1977 10 15 288 15.5 76.5 31.99 32.72109 39
1977 10 16 289 15.5 76.5 31.76 32.72109 39
1977 10 17 290 15.5 76.5 31.76 32.72109 39
1977 10 18 291 15.5 76.5 30.54 32.72109 39
1977 10 19 292 15.5 76.5 29.54 32.72109 39
1977 10 20 293 15.5 76.5 29.51 32.72109 39
1977 10 21 294 15.5 76.5 30.53 32.72109 39
1977 10 22 295 15.5 76.5 30.48 32.72109 39
1977 10 23 296 15.5 76.5 29.99 32.72109 39
1977 10 24 297 15.5 76.5 30.38 32.72109 39
1977 10 25 298 15.5 76.5 30.16 32.72109 39
1977 10 26 299 15.5 76.5 30.48 32.72109 39
1977 10 27 300 15.5 76.5 31.35 32.72109 39
1977 10 28 301 15.5 76.5 32.31 32.72109 39
1977 10 29 302 15.5 76.5 32.81 32.72109 39
1977 10 30 303 15.5 76.5 33.15 32.72109 39
1977 10 31 304 15.5 76.5 32.59 32.72109 39
1977 11  1 305 15.5 76.5 31.37 32.72109 39
1977 11  2 306 15.5 76.5 29.15 32.72109 39
1977 11  3 307 15.5 76.5 30.32 32.72109 39
1977 11  4 308 15.5 76.5 31.52 32.72109 39
1977 11  5 309 15.5 76.5 31.98 32.72109 39
1977 11  6 310 15.5 76.5 31.37 32.72109 39
1977 11  7 311 15.5 76.5 31.47 32.72109 39
1977 11  8 312 15.5 76.5 31.11 32.72109 39
1977 11  9 313 15.5 76.5 31.07 32.72109 39
1977 11 10 314 15.5 76.5  30.7 32.72109 39
1977 11 11 315 15.5 76.5 30.24 32.72109 39
1977 11 12 316 15.5 76.5 30.48 32.72109 39
1977 11 13 317 15.5 76.5 28.86 32.72109 39
1977 11 14 318 15.5 76.5 27.79 32.72109 39
1977 11 15 319 15.5 76.5 29.41 32.72109 39
1977 11 16 320 15.5 76.5 29.77 32.72109 39
1977 11 17 321 15.5 76.5 29.05 32.72109 39
1977 11 18 322 15.5 76.5 29.04 32.72109 39
1977 11 19 323 15.5 76.5 29.36 32.72109 39
1977 11 20 324 15.5 76.5 30.09 32.72109 39
1977 11 21 325 15.5 76.5  31.3 32.72109 39
1977 11 22 326 15.5 76.5 30.24 32.72109 39
1977 11 23 327 15.5 76.5 28.44 32.72109 39
1977 11 24 328 15.5 76.5 29.78 32.72109 39
1977 11 25 329 15.5 76.5 29.53 32.72109 39
1977 11 26 330 15.5 76.5 29.39 32.72109 39
1977 11 27 331 15.5 76.5 28.69 32.72109 39
1977 11 28 332 15.5 76.5 28.35 32.72109 39
1977 11 29 333 15.5 76.5  29.4 32.72109 39
1977 11 30 334 15.5 76.5  29.6 32.72109 39
1977 12  1 335 15.5 76.5 30.19 32.72109 39
1977 12  2 336 15.5 76.5 30.48 32.72109 39
1977 12  3 337 15.5 76.5 30.57 32.72109 39
1977 12  4 338 15.5 76.5 30.35 32.72109 39
1977 12  5 339 15.5 76.5 29.41 32.72109 39
1977 12  6 340 15.5 76.5 29.53 32.72109 39
1977 12  7 341 15.5 76.5  29.5 32.72109 39
1977 12  8 342 15.5 76.5  29.4 32.72109 39
1977 12  9 343 15.5 76.5 28.44 32.72109 39
1977 12 10 344 15.5 76.5  28.5 32.72109 39
1977 12 11 345 15.5 76.5 28.47 32.72109 39
1977 12 12 346 15.5 76.5 28.44 32.72109 39
1977 12 13 347 15.5 76.5 28.58 32.72109 39
1977 12 14 348 15.5 76.5 28.39 32.72109 39
1977 12 15 349 15.5 76.5 28.18 32.72109 39
1977 12 16 350 15.5 76.5 28.08 32.72109 39
1977 12 17 351 15.5 76.5 27.43 32.72109 39
1977 12 18 352 15.5 76.5  27.8 32.72109 39
1977 12 19 353 15.5 76.5 27.92 32.72109 39
1977 12 20 354 15.5 76.5 28.09 32.72109 39
1977 12 21 355 15.5 76.5 27.93 32.72109 39
1977 12 22 356 15.5 76.5 28.07 32.72109 39
1977 12 23 357 15.5 76.5 28.83 32.72109 39
1977 12 24 358 15.5 76.5  29.2 32.72109 39
1977 12 25 359 15.5 76.5 28.83 32.72109 39
1977 12 26 360 15.5 76.5 28.23 32.72109 39
1977 12 27 361 15.5 76.5 28.94 32.72109 39
1977 12 28 362 15.5 76.5  30.1 32.72109 39
1977 12 29 363 15.5 76.5 29.62 32.72109 39
1977 12 30 364 15.5 76.5 28.05 32.72109 39
1977 12 31 365 15.5 76.5 28.61 32.72109 39
1978  1  1   1 15.5 76.5 30.08 32.72109 39
1978  1  2   2 15.5 76.5 30.63 32.72109 39
1978  1  3   3 15.5 76.5  30.7 32.72109 39
1978  1  4   4 15.5 76.5  30.6 32.72109 39
1978  1  5   5 15.5 76.5 30.27 32.72109 39
1978  1  6   6 15.5 76.5 30.25 32.72109 39
1978  1  7   7 15.5 76.5 29.89 32.72109 39
1978  1  8   8 15.5 76.5 30.22 32.72109 39
end

gen ddate = mdy(MONTH, DATE, YEAR)
format ddate %td
gen TEMP_DIFF= NORMAL_TEMP - MAX_TEMP
tsset DISTRICT_ID ddate
tsspell , cond(TEMP_DIFF >= 3)
egen length = max(_seq), by(DISTRICT_ID _spell)

foreach v in _seq _end _spell {
   replace `v' = 0 if length < 3
}

list ddate *TEMP _* if _spell, sepby(_spell)

    +--------------------------------------------------------+
     |     ddate   MAX_TEMP   NORMAL~P   _seq   _spell   _end |
     |--------------------------------------------------------|
 44. | 13nov1977      28.86   32.72109      1        6      0 |
 45. | 14nov1977      27.79   32.72109      2        6      0 |
 46. | 15nov1977      29.41   32.72109      3        6      1 |
     |--------------------------------------------------------|
 48. | 17nov1977      29.05   32.72109      1        7      0 |
 49. | 18nov1977      29.04   32.72109      2        7      0 |
 50. | 19nov1977      29.36   32.72109      3        7      1 |
     |--------------------------------------------------------|
 56. | 25nov1977      29.53   32.72109      1        9      0 |
 57. | 26nov1977      29.39   32.72109      2        9      0 |
 58. | 27nov1977      28.69   32.72109      3        9      0 |
 59. | 28nov1977      28.35   32.72109      4        9      0 |
 60. | 29nov1977       29.4   32.72109      5        9      0 |
 61. | 30nov1977       29.6   32.72109      6        9      1 |
     |--------------------------------------------------------|
 66. | 05dec1977      29.41   32.72109      1       10      0 |
 67. | 06dec1977      29.53   32.72109      2       10      0 |
 68. | 07dec1977       29.5   32.72109      3       10      0 |
 69. | 08dec1977       29.4   32.72109      4       10      0 |
 70. | 09dec1977      28.44   32.72109      5       10      0 |
 71. | 10dec1977       28.5   32.72109      6       10      0 |
 72. | 11dec1977      28.47   32.72109      7       10      0 |
 73. | 12dec1977      28.44   32.72109      8       10      0 |
 74. | 13dec1977      28.58   32.72109      9       10      0 |
 75. | 14dec1977      28.39   32.72109     10       10      0 |
 76. | 15dec1977      28.18   32.72109     11       10      0 |
 77. | 16dec1977      28.08   32.72109     12       10      0 |
 78. | 17dec1977      27.43   32.72109     13       10      0 |
 79. | 18dec1977       27.8   32.72109     14       10      0 |
 80. | 19dec1977      27.92   32.72109     15       10      0 |
 81. | 20dec1977      28.09   32.72109     16       10      0 |
 82. | 21dec1977      27.93   32.72109     17       10      0 |
 83. | 22dec1977      28.07   32.72109     18       10      0 |
 84. | 23dec1977      28.83   32.72109     19       10      0 |
 85. | 24dec1977       29.2   32.72109     20       10      0 |
 86. | 25dec1977      28.83   32.72109     21       10      0 |
 87. | 26dec1977      28.23   32.72109     22       10      0 |
 88. | 27dec1977      28.94   32.72109     23       10      1 |
     |--------------------------------------------------------|
 90. | 29dec1977      29.62   32.72109      1       11      0 |
 91. | 30dec1977      28.05   32.72109      2       11      0 |
 92. | 31dec1977      28.61   32.72109      3       11      1 |
     +--------------------------------------------------------+

Missing temperatures require extra care. See Clyde's answer for what to do.

Note further that

gen byte exceeds =1 if TEMP_DIFF>3
replace exceeds =1 if TEMP_DIFF==3
replace exceeds =0 if exceeds==.

reduces to

gen exceeds = TEMP_DIFF >= 3

[Jaweriah Abdullah]

Dear Sir
Thank you for showing how to use tsspell. Also you are right working with the date variable is easier
(I adjusted the variable TEMP_DIFF) for heat waves.

Very helpful as always