Creating Variable pre and post event time with

[John Singer]

Hello,
I have a panel dataset at the year-quarter-id level. I would like to create an indicator variable that takes ranges from -3 to 3, with the event occurring at 0.
I have found an old solution here , and it almost works.

Below is the code, copied from the linked solution:

gen prev = year if event == 1
bysort id year (prev) : replace prev = prev[1] if prev[1] == 1
bysort id (year) : replace prev = prev[_n-1] if mi(prev)
gen forward = year - prev

gen negdate = -year
gen next = year if event == 1
bysort id negdate (year) : replace next = next[1] if next[1] == 1
bysort id (negdate) : replace next = next[_n-1] if mi(next)
gen backward = year - next

gen timeline = cond(abs(forward) <= abs(backward), forward, backward)

A limited example is below, with an event in 1974. As you can see, for some IDs there are missing variables. I think this is because there are some missing quarter-years for some variables. For example, I looked at id = 71 and id=97 for the two years, and while 71 had the full 8 quarter-year observations, 97 only had 6. Importantly, 97 also didn't have an observation in the event year.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id event) str6 date int prev byte forward int(negdate next) byte(backward timeline)
71 0 "1970q1"    . . -1970 1974 -4 -4
71 0 "1970q2"    . . -1970 1974 -4 -4
71 0 "1970q3"    . . -1970 1974 -4 -4
71 0 "1970q4"    . . -1970 1974 -4 -4
71 1 "1974q1" 1974 0 -1970 1974  0  0
71 1 "1974q2" 1974 0 -1970 1974  0  0
71 1 "1974q3" 1974 0 -1970 1974  0  0
71 1 "1974q4" 1974 0 -1970 1974  0  0
97 0 "1969q1"    . . -1969    .  .  .
97 0 "1969q2"    . . -1969    .  .  .
97 0 "1969q3"    . . -1969    .  .  .
97 0 "1969q4"    . . -1969    .  .  .
97 0 "1970q1"    . . -1970    .  .  .
97 0 "1970q1"    . .  1970    .  .  .
end

Any suggestions welcome! Happy to use more concise code, as well. Thank you.

Edit: tried to fix title (was supposed to say "with unbalanced panel" but can no longer edit title)

[William Lisowski]

Perhaps this example will start you in a useful direction.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id event) str6 date
71 0 "1970q1"
71 0 "1970q2"
71 0 "1970q3"
71 0 "1970q4"
71 1 "1974q1"
71 1 "1974q2"
71 1 "1974q3"
71 1 "1974q4"
97 0 "1969q1"
97 0 "1969q2"
97 0 "1969q3"
97 0 "1969q4"
97 0 "1970q1"
97 0 "1970q1"
end
generate qtr = quarterly(date,"YQ")
format qtr %tq
drop date

bysort id (qtr): egen eventqtr = min(cond(event==1,qtr,.))
format eventqtr %tq
generate diff = qtr-eventqtr
generate wanted = cond(inrange(diff,-3,3),diff,.)
list, sepby(id) abbreviate(12)

. list, sepby(id) abbreviate(12)

     +------------------------------------------------+
     | id   event      qtr   eventqtr   diff   wanted |
     |------------------------------------------------|
  1. | 71       0   1970q1     1974q1    -16        . |
  2. | 71       0   1970q2     1974q1    -15        . |
  3. | 71       0   1970q3     1974q1    -14        . |
  4. | 71       0   1970q4     1974q1    -13        . |
  5. | 71       1   1974q1     1974q1      0        0 |
  6. | 71       1   1974q2     1974q1      1        1 |
  7. | 71       1   1974q3     1974q1      2        2 |
  8. | 71       1   1974q4     1974q1      3        3 |
     |------------------------------------------------|
  9. | 97       0   1969q1          .      .        . |
 10. | 97       0   1969q2          .      .        . |
 11. | 97       0   1969q3          .      .        . |
 12. | 97       0   1969q4          .      .        . |
 13. | 97       0   1970q1          .      .        . |
 14. | 97       0   1970q1          .      .        . |
     +------------------------------------------------+

[John Singer]

Hi William, thanks for this. Is there a way to get the "wanted" varable to move by years rather than by quarters? In the results you poste, all of 1974q1 should have the same value (0).

[William Lisowski]

At this point I do not understand your data and your problem well enough to contribute further.

Because your too-limited example data presents no case where, having become 1, the event variable returns to 0, I assumed that it was coded in the common manner of being 0 before the quarter in which the event occurred and 1 in the quarter of the event and in all following quarters. And because you had quarterly data, I assumed you want 3 to indicate 3 quarters after the event. And in your description you say ID 71 "had the full 8 quarter-years of observations", which seems incorrect, since the 8 quarters of 1970 and 1974 are separated by 12 missing quarters.

With the sort of event you seem to describe, which occurs in more than one quarter, questions arise. In particular, can a given ID have multiple distinct events - 1's separated by 0's? If so, what if the second event begins 3 quarters after the first event ends - do you count backward to the event that ended or forward to the event that will soon begin?

You need to do a better job of explaining your data and explaining precisely what you seek.

With that said, the essence of my code in post #2 is that since you have ID's with gaps in their observations - ID 71 missing data from 1971-1973 for example - you should use the quarterly date to measure how many periods apart two observations are.