Extending dummy variable flag over multiple dates

[Joshua Evanss]

Hello,

I have a panel dataset comprising three variables:

1. ID
2. Date-quarter
3. transdummy

transdummy is a flag that identifies whether M&A activity has occurred for a particular ID (bank) in a particular quarter.

If M&A activity has occurred (transdummy = 1), I want to make transdummy = 1 in the following 12 quarters. This way, transdummy would equal 1 if M&A activity has occurred for a particular ID in any of the past 12 quarters (3 years).

For example, if transdummy = 1 in 2000:Q1. I want to make transdummy = 1 in 2000:Q2, 2000:Q3, ... 2003:Q1.

I tried the following

         bysort rssd9001: gen transdummy2 = 1 if transdummy[_n-12] == 1 |  ///
            transdummy[_n-11] == 1 |       ///
            transdummy[_n-10] == 1 |       ///
            transdummy[_n-9] == 1 |        ///
            transdummy[_n-8] == 1 |        ///
            transdummy[_n-7] == 1 |        ///
            transdummy[_n-6] == 1 |        ///
            transdummy[_n-5] == 1 |        ///
            transdummy[_n-4] == 1 |        ///
            transdummy[_n-3] == 1 |        ///
            transdummy[_n-2] == 1 |        ///
            transdummy[_n-1] == 1 |        ///
            transdummy == 1

drop transdummy
rename transdummy2 transdummy

However, the desired result was not achieved where the Date-quarter observations for a particular ID was patchy (i.e. first obs is 2000:Q1, second obs is 2004:Q3).

I would really appreciate any help on how I can solve this problem. It seems pretty straightforward and is probably an easy problem for an experienced Stata user.

Thanks.

[Wouter Wakker]

You don't give a data example so I'm assuming that you already have your date variable as Stata quarterly dates. In that case it can be done with rangestat from SSC (ssc install rangestat).

rangestat (max) wanted=transdummy, interval(dateq, -12, 0) by(id)

[Joshua Evanss]

You don't give a data example so I'm assuming that you already have your date variable as Stata quarterly dates. In that case it can be done with rangestat from SSC (ssc install rangestat).

rangestat (max) wanted=transdummy, interval(dateq, -12, 0) by(id)

Thanks Wouter. You are correct, my date variable is already in %tq format. I directly copied the code you provided, and it worked perfectly. Thank you!