Identify backward time span from an event date

[Alberto Alvarez]

Dear Stata Users,
Please help me with the following issue.
I have a firm identifier (gvkey), year-month (fdate), shock (shock), production level (production) and identifier of a firm that experienced a shock (shock_firm). I need to study how the production was changing 10, 9, 8,… 1 months before the shock. Basically, I wanted to create a variable specifying the backward date, something like in the example below:

Firm	Shock	Back_date
A	0	-6
A	0	-5
A	0	-4
A	0	-3
A	0	-2
A	0	-1
A	1	0
A	0	1
A	0	2
A	0	3
A	0	4

But the problem is that I have some firms that more than one shock year and they overlap. For instance:

Firm	Shock	Back_date
A	0	-6
A	0	-5
A	0	-4
A	0	-3
A	0	-2
A	0	-1
A	1	0
A	0	1
A	1	2
A	0	3
A	0	4

I thought to introduce Back_date_1 and Back_date_2 for two different shock events (example below), but I have a maximum of 41 shock events that will complicate further use of these variables. Is there any other way I can create a back_date variable accounting for this circumstance?

Firm	Shock	Back_date_1	Back_date_2
A	0	-6	-8
A	0	-5	-7
A	0	-4	-6
A	0	-3	-5
A	0	-2	-4
A	0	-1	-3
A	1	0	-2
A	0	1	-1
A	1	2	0
A	0	3	1
A	0	4	2

* Example generated by -dataex-. To install: ssc install dataex
clear
input str24 gvkey float(fdate shock shock_firm production)
"025279" 420 0 1   .3488717
"025279" 421 0 1   .2668857
"025279" 422 1 1   .1366463
"025279" 423 0 1 .028556867
"025279" 424 0 1   .8689333
"025279" 425 0 1   .3508549
"025279" 426 0 1  .07110509
"025279" 427 0 1  .32336795
"025279" 428 0 1   .5551032
"025279" 429 0 1    .875991
"025279" 430 0 1  .20470947
"025279" 431 1 1   .8927587
"025279" 432 0 1   .5844658
"025279" 433 0 1   .3697791
"025279" 434 1 1   .8506309
"025279" 435 0 1   .3913819
"025279" 436 0 1  .11966132
"025279" 437 1 1   .7542434
"025279" 438 0 1   .6950234
"025279" 439 0 1   .6866152
"102324" 423 0 1   .9319346
"102324" 424 0 1   .4548882
"102324" 425 0 1   .0674011
"102324" 426 0 1   .3379889
"102324" 427 0 1   .9748848
"102324" 428 0 1   .7264384
"102324" 429 0 1  .04541512
"102324" 430 0 1   .7459667
"102324" 431 1 1   .4961259
"102324" 432 0 1   .7167162
"102324" 433 0 1    .859742
end

[Nick Cox]

I answered a similar question a few weeks ago, but I can't find it. Consider

1. Short answer: To count backwards, reverse time first.

2. Longer answer:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str24 gvkey float(fdate shock shock_firm production)
"025279" 420 0 1   .3488717
"025279" 421 0 1   .2668857
"025279" 422 1 1   .1366463
"025279" 423 0 1 .028556867
"025279" 424 0 1   .8689333
"025279" 425 0 1   .3508549
"025279" 426 0 1  .07110509
"025279" 427 0 1  .32336795
"025279" 428 0 1   .5551032
"025279" 429 0 1    .875991
"025279" 430 0 1  .20470947
"025279" 431 1 1   .8927587
"025279" 432 0 1   .5844658
"025279" 433 0 1   .3697791
"025279" 434 1 1   .8506309
"025279" 435 0 1   .3913819
"025279" 436 0 1  .11966132
"025279" 437 1 1   .7542434
"025279" 438 0 1   .6950234
"025279" 439 0 1   .6866152
"102324" 423 0 1   .9319346
"102324" 424 0 1   .4548882
"102324" 425 0 1   .0674011
"102324" 426 0 1   .3379889
"102324" 427 0 1   .9748848
"102324" 428 0 1   .7264384
"102324" 429 0 1  .04541512
"102324" 430 0 1   .7459667
"102324" 431 1 1   .4961259
"102324" 432 0 1   .7167162
"102324" 433 0 1    .859742
end

bysort gvkey (fdate): gen counter = sum(shock==1) if shock==1

gsort gvkey -fdate

su counter, meanonly
local smax = r(max)

quietly forval s = 1/`smax' {
    gen wanted`s' = 0 if counter == `s'
    by gvkey: replace wanted`s' = wanted`s'[_n-1] - 1 if missing(wanted`s')
}

sort gvkey fdate

quietly forval s = 1/`smax' {
    by gvkey: replace wanted`s' = wanted`s'[_n-1] + 1 if missing(wanted`s')
}

list gvkey counter wanted*, sepby(gvkey)


     +----------------------------------------------------------+
     |  gvkey   counter   wanted1   wanted2   wanted3   wanted4 |
     |----------------------------------------------------------|
  1. | 025279         .        -2       -11       -14       -17 |
  2. | 025279         .        -1       -10       -13       -16 |
  3. | 025279         1         0        -9       -12       -15 |
  4. | 025279         .         1        -8       -11       -14 |
  5. | 025279         .         2        -7       -10       -13 |
  6. | 025279         .         3        -6        -9       -12 |
  7. | 025279         .         4        -5        -8       -11 |
  8. | 025279         .         5        -4        -7       -10 |
  9. | 025279         .         6        -3        -6        -9 |
 10. | 025279         .         7        -2        -5        -8 |
 11. | 025279         .         8        -1        -4        -7 |
 12. | 025279         2         9         0        -3        -6 |
 13. | 025279         .        10         1        -2        -5 |
 14. | 025279         .        11         2        -1        -4 |
 15. | 025279         3        12         3         0        -3 |
 16. | 025279         .        13         4         1        -2 |
 17. | 025279         .        14         5         2        -1 |
 18. | 025279         4        15         6         3         0 |
 19. | 025279         .        16         7         4         1 |
 20. | 025279         .        17         8         5         2 |
     |----------------------------------------------------------|
 21. | 102324         .        -8         .         .         . |
 22. | 102324         .        -7         .         .         . |
 23. | 102324         .        -6         .         .         . |
 24. | 102324         .        -5         .         .         . |
 25. | 102324         .        -4         .         .         . |
 26. | 102324         .        -3         .         .         . |
 27. | 102324         .        -2         .         .         . |
 28. | 102324         .        -1         .         .         . |
 29. | 102324         1         0         .         .         . |
 30. | 102324         .         1         .         .         . |
 31. | 102324         .         2         .         .         . |
     +----------------------------------------------------------+

.There's a more general answer if there are gaps in any panel.

[Alberto Alvarez]

Thank you very much for your reply! I made something similar, but just for a few cases (max "counter" == 3) did not know how to generate (wanted`s') automatically. I was wandering if there is a way to ttest production=0 if wanted`s'==-1

ttest production=0 if wanted`s'==-1

Like to ttest production for all the values of wanted`s' that are equal to "-1".

Thank you for your help!

[Nick Cox]

I don't see a question in #3. The syntax looks legal. I don't know how interesting or useful the test would be.

[Alberto Alvarez]

Dear Nick,

Thank you for your reply. I am just trying to estimate t-test to get an idea whether the means of production for pre-shock window are statistically different from zero. Say, if I have something like in above dataset and what to t-test production for “wanted`s’==-1” I will take all the observations that are highlighted below. I can do something like this:

ttest production=0 if wanted1==-1| wanted2==-1| wanted3==-1| wanted4==-1

But if there are many wanted`s’, say max `s’ == 40, this might be tedious to type all wanted`s’. Is there is a way to estimate ttest using a short-cut?

+----------------------------------------------------------+
| gvkey counter wanted1 wanted2 wanted3 wanted4 |
|----------------------------------------------------------|
1. | 025279 . -2 -11 -14 -17 |
2. | 025279 . -1 -10 -13 -16 |
3. | 025279 1 0 -9 -12 -15 |
4. | 025279 . 1 -8 -11 -14 |
5. | 025279 . 2 -7 -10 -13 |
6. | 025279 . 3 -6 -9 -12 |
7. | 025279 . 4 -5 -8 -11 |
8. | 025279 . 5 -4 -7 -10 |
9. | 025279 . 6 -3 -6 -9 |
10. | 025279 . 7 -2 -5 -8 |
11. | 025279 . 8 -1 -4 -7 |
12. | 025279 2 9 0 -3 -6 |
13. | 025279 . 10 1 -2 -5 |
14. | 025279 . 11 2 -1 -4 |
15. | 025279 3 12 3 0 -3 |
16. | 025279 . 13 4 1 -2 |
17. | 025279 . 14 5 2 -1 |
18. | 025279 4 15 6 3 0 |
19. | 025279 . 16 7 4 1 |
20. | 025279 . 17 8 5 2 |
|----------------------------------------------------------|
21. | 102324 . -8 . . . |
22. | 102324 . -7 . . . |
23. | 102324 . -6 . . . |
24. | 102324 . -5 . . . |
25. | 102324 . -4 . . . |
26. | 102324 . -3 . . . |
27. | 102324 . -2 . . . |
28. | 102324 . -1 . . . |
29. | 102324 1 0 . . . |
30. | 102324 . 1 . . . |
31. | 102324 . 2 . . . |
+----------------------------------------------------------+

[Nick Cox]

Use egen‘s anyvalue() function to identify the observations you want.

[Alberto Alvarez]

Thank you for your reply! I generated the code, but I do not know how to replace "my_observation" after "wanted`s' " == 1. It just generates "wanted" for `s'==1. So I need to tell STATA replace "my_observation" after it finishes with `s'==1 and turns to `s'==2,3,etc. Can you please elaborate a bit on this issue?

forval s = 1/`smax' {
    replace egen my_observation = anyvalue(wanted`s'), v(-1)
}

[Nick Cox]

Sorry, I was replying by phone. This was the kind of thing I had in mind

egen mysample = anymatch(wanted*), value(-1)
  
ttest production=0 if mysample

[Ali Atia]

egen my_observation=anymatch(wanted*), values(-1)
ttest production = 0 if my_observation == 1

[Alberto Alvarez]

Thank you! It worked perfectly.