Problem with data manipulation in panel data

[Fenne Jonkers]

Hi,

I am using StataMP 17 on Windows 10. I am working in a database with 27,672 observations and seven variables.

I have a dataset with M&A transactions and the acquiring company's financial advisor. My goal is to add a dummy variable indicating whether the financial advisor has been a bookrunner in an equity issuance for the acquiring company over the five years before the M&A transaction (1) or not (0).

I now have a dataset with the M&A transactions and the equity issuances and their advisors/bookrunners. I sorted the dataset on acquiringcompany and date resulting in the following dataset:

input str6 AcquirorCUSIP float Date str30(AcquirorAdvisors1 Bookrunners1 Bookrunners2) float(MA EI)
"000400" 12557 "" "Sterne Agee & Leach Inc" "" 0 1
"000400" 15027 "Sterne Agee & Leach Inc" "" "" 1 0
"000400" 16618 "SunTrust" "" "" 1 0
"000886" 10664 "Goldman Sachs" "" "" 1 0
"000886" 12942 "" "Lehman Brothers" "" 0 1
"000886" 14138 "CIBC" "" "" 1 0
"000886" 14416 "Credit Suisse" "" "" 1 0
"000886" 14663 "Lehman Brothers" "" "" 1 0
"000886" 14735 "Credit Suisse" "" "" 1 0
"000886" 14873 "Lehman Brothers" "" "" 1 0
"000886" 15011 "Lehman Brothers" "" "" 1 0
"000886" 15854 "" "Bank of America" "Credit Suisse" 0 1
"000886" 16155 "Dresdner Kleinwort" "" "" 1 0
"000886" 16638 "Houlihan Lokey" "" "" 1 0
"000886" 17461 "Houlihan Lokey" "" "" 1 0
"000886" 17482 "Houlihan Lokey" "" "" 1 0
"000886" 17519 "" "Credit Suisse" "Morgan Stanley" 0 1

with
AcquirorAdvisors1 indicating the M&A financial advisor
Bookrunners1 and Bookrunners2 indicating the bookrunners for the equity issuance
MA (dummy variable) is 1 if the observation is an M&A transaction
EI (dummy variable) is 1 if the observation is an Equity Issuance

My goal is to create a dataset with only the M&A transactions and their financial advisors with a dummy variable indicating whether this financial advisor has been a bookrunner to the acquiring company in an equity issuance in the five years before the M&A transaction. This is what i want to achieve:

input str6 AcquirorCUSIP float Date str30(AcquirorAdvisors1) float(DumEquity)
"000400" 15027 "Sterne Agee & Leach Inc" 1
"000400" 16618 "SunTrust" 0
"000886" 10664 "Goldman Sachs" 0
"000886" 14138 "CIBC" 0
"000886" 14416 "Credit Suisse" 0
"000886" 14663 "Lehman Brothers" 1
"000886" 14735 "Credit Suisse" 0
"000886" 14873 "Lehman Brothers" 0
"000886" 15011 "Lehman Brothers" 0
"000886" 16155 "Dresdner Kleinwort" 0
"000886" 16638 "Houlihan Lokey" 0
"000886" 17461 "Houlihan Lokey" 0
"000886" 17482 "Houlihan Lokey" 0

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

[Clyde Schechter]

//  CREATE A TEMPFILE WITH JUST EQUITY ISSUANCES AND THEIR BOOKRUNNERS
preserve
tempfile equity_issuances
keep if EI == 1
keep Date Bookrunners*
gen long obs_no = _n
reshape long Bookrunners, i(obs_no)
drop if missing(Bookrunners)
keep Date Bookrunners
rename Bookrunners AcquirorAdvisors1
duplicates drop
save `equity_issuances'
restore

//  COMBINE THE TWO FILES
drop Bookrunners*
keep if MA == 1
local 5years = int(5*365.25)
rangejoin Date -`5years' 0 using `equity_issuances', by(AcquirorAdvisors1)

-rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, but Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

[Fenne Jonkers]

Thank you so much! This helped a lot!

I made a minor alteration as I only wanted to include the equity issuances where the advisor advising the M&A transaction had been a bookrunner to the acquiring company itself, and not a bookrunner in general. I have noted my alterations in bold.

Code:
// CREATE A TEMPFILE WITH JUST EQUITY ISSUANCES AND THEIR BOOKRUNNERS
preserve
tempfile equity_issuances
keep if EI == 1
keep AcquirorCUSIP Date Bookrunners*
gen long obs_no = _n
reshape long Bookrunners, i(obs_no)
drop if missing(Bookrunners)
keep AcquirorCUSIP Date Bookrunners
rename Bookrunners AcquirorAdvisors1
rename AcquirorCUSIP acquirorcusip
duplicates drop
save `equity_issuances'
restore

// COMBINE THE TWO FILES
drop Bookrunners*
keep if MA == 1
local 5years = int(5*365.25)
rangejoin Date -`5years' 0 using `equity_issuances', by(AcquirorAdvisors1)
keep if AcquirorCUSIP == acquirorcusip