Hi,


I am a STATA newbie and got somehow stuck merging two datasets.

One dataset (A) contains, among other things, the announcement date of a merger of a listed company as well as its ticker symbol.

Code:
clear
input int DateAnnounced str6 AcquirorPrimaryTickerSymbol
14619 "AOL"  
14619 "NXLK"  
14619 "CAFC"  
22001 "6MK"
14621 "USAI"  
14622 "BFOH"  
14622 "SCHW34"
14623 "EQR"  
14624 "MWL"  
14626 "JDSU"  
end
format %tdDD.NN.CCYY DateAnnounced
The second dataset (B) contains information on the history of a company's financial managers, as well as the time period during which the manager was in place and again the company's ticker.

Code:
clear
input str27 DirectorName str105 CompanyName str84 RoleName int(DateStartRole DateEndRole) str9 Ticker
"Jack Wyszomierski"   "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             13149 16026 "6MK" 
"Peter Kellogg"       "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO/Principal Financial Officer" 18204 19836 "6MK" 
"Bob Bertolini"       "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             16026 18204 "6MK" 
"Robert Davis"        "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             19836 22371 "6MK" 
"Caroline Litchfield" "MERCK & CO INC (Schering-Plough Corp prior to 11/2009)" "Executive VP/CFO"                             22371     . "6MK" 
"Ira Zar"             "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO"                             14335 15979 "9C7" 
"Jeff Clarke"         "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO/COO"                         16187 16496 "9C7" 
"Nancy Cooper"        "CA INC (CA Technologies) (De-listed 11/2018)"           "Executive VP/CFO"                             17028 18765 "9C7" 
"Robin Elliott"       "FIRST BUSEY CORP"                                       "Director - Financial Reporting"               16922 18628 "BUSE"
end
format %tdDD.NN.CCYY DateStartRole
format %tdDD.NN.CCYY DateEndRole
I created two frames and imported each dataset in one frame. ("Mergers" and "Managers").

I would like to find out which financial manager (Dataset B) was in place at the time of the announcement of a deal (dataset A).
More precisely: In Dataset A a new variable "StartDate" should be defined using the following logic: Search Dataset B for entries about the corresponding ticker (6MK, for example). From the entries related to this ticker, return the start date (DateStartRole) that falls before the deal announcement date, and that minimizes the difference: Announcement Date - DateStartRole. This ensures that we will get the start date of the person who was most recently appointed as the Finance Manager before the deal announcement.

Following a similar approach, a corresponding variable "EndDate" should be defined in dataset A as follows: Again, search in dataset B for entries to the corresponding ticker. From those entries, output the end date (DateEndRole) that lies behind the deal announcement date, minimizing the difference: DateEndRole - AnnouncementDate. If no value is assigned to DateEndRole, the manager is still in place. In this case, DateEndRole can be set equal to today's date. The time span from the two dates (DateEndRole-DateStartRole) is the tenure of the CFO who held that position through the deal announcement date. Last step: Define a new variable in dataset A (CFO name) and output the name of the corresponding CFO assigned to DateEndRole or DateStarRole (i.e. the CFO who was in office during the deal announcement).

For example: Ticker 6MK announces a deal on 27.03.2020. In this case, the StartDate variable should take the value 23.04.2014 and the EndDate variable should take the value 01.04.2021. The CFO in place at that time is therefore Robert Davis.

I would really appreciate if someone could help me out with the code or at least with some information about the necessary stata commands.

Version: 16.1

Thank you very much
Best