three-year mean before an event

[River Huang]

Dear All, I found this question here (in Chinese). The data set is

* Example generated by -dataex-. For more info, type help dataex
clear
input long stkcd int year str4 ma float rd
8 2009 "#N/A"        0
8 2010 "#N/A"        0
8 2011 "#N/A"        0
8 2012 "#N/A"        0
8 2013 "#N/A"        0
8 2014 "#N/A"        0
8 2015 "#N/A"        0
8 2016 "YES"   7367428
8 2017 "#N/A" 17148702
8 2018 "#N/A"  7281274
8 2019 "#N/A" 10828121
9 2009 "#N/A"  2930650
9 2010 "#N/A"  3629185
9 2011 "#N/A"  5196702
9 2012 "#N/A"  5190145
9 2013 "#N/A"  3628819
9 2014 "#N/A"  5801677
9 2015 "YES"  10642266
9 2016 "#N/A" 15253602
9 2017 "#N/A" 18787858
9 2018 "#N/A" 24116868
9 2019 "#N/A" 24998574
end

Suppose that there is (at most) one merger and/or acquisition (MA, denoted by the `ma' variable) per `stkcd' across years. MA occurred in the year when "YES" appeared. I'd like to have the following two variables (in the MA year).

1. I'd like to calculate the mean of `rd' over three years before the MA year.
2. I'd like to calculate the mean of `rd' over three years after the MA year.

[Ali Atia]

bys stkcd (year): egen x = max(cond(ma=="YES",year,.))
bys stkcd  (year): egen before = mean(cond(year>=(x-3) & year<x,rd,.))
bys stkcd  (year): egen after = mean(cond(year<=(x+3) & year>x,rd,.))
drop x

[Clyde Schechter]

//  VERIFY ONE AND ONLY ONE MA PER STKCD
by stkcd (year), sort: egen n_ma = total(ma == "YES")
assert n_ma == 1
drop n_ma

//  OBTAIN YEAR OF MA FOR EACH STKCD
by stkcd (year): egen ma_year = max(cond(ma == "YES", year, .))

//  OBTAIN AVERAGE RD 3 YEARS BEFORE AND AFTER
by stkcd (year): egen mean_rd_3_before = mean(cond(inrange(ma_year-year, 1, 3), rd, .))
by stkcd (year): egen mean_rd_3_after = mean(cond(inrange(year-ma_year, 1, 3), rd, .))

Added: Crossed with #2 which offers a slight notational variant of the same solution.

[River Huang]

Dear @Ali Atia, and @Clyde Schechter, Thanks a lot for your helpful suggestions.

[Nick Cox]

tsset stkcd year

gen wanted1 = (L3.rd + L2.rd + L1.rd)/3 if ma == "YES"

gen wanted2 = (F3.rd + F2.rd + F1.rd)/3 if ma == "YES"

Naturally, if the tsset fails, this code is too optimistic about the data.

[River Huang]

Dear Nick, Thanks also for this helpful method.