Moving sum in unbalanced panel dataset over specific timeperiod

[Fenne Jonkers]

Hi,

I am using StataMP 17 on Windows 11. I am working in a database with 44,174 observations and three variables. The time-period of my dataset is 1985-2020.

I have a dataset with M&A transactions with the following three variables: announcement date, company cusip and transaction value. My goal is to create a new variable that represents the sum of the transaction value of all transactions that have taken place in the prior three years before the current acquisition.

Please find below an overview of my current dataset.

input double DateAnnounced str6 CUSIP double ValueofTransactionmil
9133 "72148K" 17
9133 "14842Q" 40
9135 "07655Q" 77
9138 "904671" 128.61
9138 "435081" 308.7
9139 "880370" 52
9139 "40414R" 88.75
9139 "018635" 125
9139 "57327A" 400
9140 "339720" 200
9141 "594787" 46.375
9141 "873449" 48
9141 "55268Q" 200
9142 "863200" 40.3
9145 "65438Q" 473
9145 "482584" 500
9146 "989280" 55
9146 "01750M" 1550
9147 "46145M" 125
9147 "904671" 188.5
9147 "526768" 300
9148 "01750M" 1650
9149 "879131" 75
9149 "37428K" 95
9149 "817715" 225
9152 "03070V" 11.2

As my paneldata is unbalanced (i.e. multiple observations on some dates and zero observations on other dates) I am having problems creating this three-year moving sum.

Does anyone know how to achieve this or in which direction I should look? Also, please let me know if anything is unclear or needs further explanation.

Thank you in advance!

Fenne

[Nick Cox]

See rangestat from SSC you must install before you can use it (you can search this forum for mentions too).

If I understand this correctly you want something like either or both of

rangestat (sum) Total=Value, interval(Date -1095 0)

rangestat (sum) Total2=Value, interval(Date -1095 0) by(CUSIP)

where 1095 is an interpretation for daily dates of 3 years ago, 0 includes the present date (-1 would exclude it) and by(CUSIP) may or may not be needed. I know only just enough economics to guess what M & A means; it is risky to assume on Statalist that people work in your sub-sub-field and recognise its jargon. .

Hope this helps.

[Fenne Jonkers]

Thank you for your quick answer! It works perfectly.

And thank you for your note regarding the M&A abbreviation. I will take this into account in further questions.