Creating lists of observations for which a specific condition holds

[Klaus Klausen]

Hi,

I am already trying all day on the following problem and am very grateful for any help. I do my best to give all the necessary information without going too much into unnecessary details. Please let me know if there is something unclear.

I have a dataset which contains the employment history of about 6000 CFOs of US companies:

* Example generated by -dataex-. For more info, type help dataex
clear
input long DirectorID str26 DirectorName str128 CompanyName str74 RoleName long CompanyID int(DateStartRole DateEndRole) str33(HOCountryName Sector)
323324 "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "CFO/Secretary/Treasurer"          22923 13402 16284 "United States"            "Leisure & Hotels"
323324 "A Fitzhugh" "Southern Land Company LLC"                             "CFO"                            1497521 16376 17837 "United States"            ""                
323324 "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "Controller"                       22923  9862 11048 "United States"            "Leisure & Hotels"
323324 "A Fitzhugh" "JASON Foundation"                                      "Director - ED"                    78144 14976 21550 "United States"            ""                
323324 "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "Secretary/Treasurer/Controller"   22923 12174 13402 "United States"            "Leisure & Hotels"
323324 "A Fitzhugh" "Cosi Inc"                                              "Interim CFO"                    2539052 21173 22504 "United States"            "Leisure & Hotels"
323324 "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "Controller/Treasurer"             22923 11048 12174 "United States"            "Leisure & Hotels"
275461 "A Leighton" "SONIC SOLUTIONS (De-listed 02/2011)"                   "Senior VP/CFO"                    28575 14245 16705 "United States"            "Business Services"
275461 "A Leighton" "San Francisco Vikings Youth Soccer League"             "CFO"                             562416 18993 19724 "United States"            ""                
275461 "A Leighton" "Boston Consulting Group (BCG) Inc"                     "Consultant"                       35643    .n 22504 "United States"            "Business Services"
275461 "A Leighton" "SONIC SOLUTIONS (De-listed 02/2011)"                   "Vice President - Finance"         28575 12085 14245 "United States"            "Business Services"
275461 "A Leighton" "SONIC SOLUTIONS (De-listed 02/2011)"                   "Executive VP/COO"                 28575 17587 18676 "United States"            "Business Services"
275461 "A Leighton" "SONIC SOLUTIONS (De-listed 02/2011)"                   "Executive VP/CFO"                 28575 16705 17587 "United States"            "Business Services"
275461 "A Leighton" "San Francisco Vikings Youth Soccer League"             "Director - Non-Brd"              562416 19724 22504 "United States"            ""                
275461 "A Leighton" "San Francisco Vikings Youth Soccer League"             "Vice President"                  562416 18628 18993 "United States"            ""                
275461 "A Leighton" "Resna Industries Inc"                                  "VP/CFO"                          562411 10958 11870 "United States"            ""                
342193 "A O'Connor" "Tidewater Utilities Inc"                               "Treasurer"                       631094 16131 21550 "United States"            "Utilities - Other"
342193 "A O'Connor" "Deloitte LLP (Deloitte & Touche LLP prior to 12/2008)" "Various Positions"                35734  8766 10958 "United Kingdom - England" "Business Services"
342193 "A O'Connor" "Utility Service Affiliates (Perth Amboy) Inc"          "VP/Treasurer"                    631182 16131 22504 "United States"            ""                
342193 "A O'Connor" "White Marsh Environmental Systems Inc"                 "Treasurer"                       631095    .n 16131 "United States"            ""                
342193 "A O'Connor" "Bayview Water Co"                                      "Treasurer"                       631183 16131 16071 "United States"            ""                
342193 "A O'Connor" "MIDDLESEX WATER CO"                                    "Controller"                       20638 11688 12784 "United States"            "Utilities - Other"
342193 "A O'Connor" "Tidewater Utilities Inc"                               "Treasurer"                       631094    .n 16131 "United States"            "Utilities - Other"
342193 "A O'Connor" "Pinelands Wastewater Co"                               "VP/Treasurer"                    631179    .n 16131 "United States"            ""                
342193 "A O'Connor" "MIDDLESEX WATER CO"                                    "VP/CFO"                           20638 16253 19783 "United States"            "Utilities - Other"
342193 "A O'Connor" "Tidewater Environmental Services Inc"                  "Treasurer"                       631097 16131 22504 "United States"            "Business Services"
342193 "A O'Connor" "Pinelands Water Co"                                    "VP/Treasurer"                    632212    .n 16131 "United States"            ""                
342193 "A O'Connor" "MIDDLESEX WATER CO"                                    "VP/CFO/Treasurer"                 20638 19783 21550 "United States"            "Utilities - Other"
342193 "A O'Connor" "MIDDLESEX WATER CO"                                    "Vice President"                   20638 12784 13270 "United States"            "Utilities - Other"
342193 "A O'Connor" "Pinelands Wastewater Co"                               "VP/Treasurer"                    631179 16131 22504 "United States"            ""                
342193 "A O'Connor" "Bayview Water Co"                                      "Treasurer"                       631183    .n 16131 "United States"            ""                
342193 "A O'Connor" "Pinelands Water Co"                                    "VP/Treasurer"                    632212 16131 22504 "United States"            ""                
342193 "A O'Connor" "Utility Service Affiliates (Perth Amboy) Inc"          "VP/Treasurer"                    631182    .n 16131 "United States"            ""                
342193 "A O'Connor" "Tidewater Environmental Services Inc"                  "Treasurer"                       631097    .n 16131 "United States"            "Business Services"
342193 "A O'Connor" "MIDDLESEX WATER CO"                                    "Assistant Controller"             20638 10958 11688 "United States"            "Utilities - Other"
end
format %td DateStartRole
format %td DateEndRole
compress

I merged this dataset with another dataset that contains information about Mergers & Acquisitions (M&A) of selected companies where the respective manager worked.
The variables DirectorID up to and including Sector belong to the first data set. The description of the remaining variables that were merged are as follows:

• Deal_No (unique identifier of a Deal that serves as a link between the first and second dataset)
• Deal_Announced (Date on which the Deal was announced)
• Acq_ID (unique identifier of the Acquiror in the Deal, essentially the same as CompanyID)
• CFO_ID (unique identifier of the CFO that was in place when the Deal was announced, essentially the same as DirectorID)
• CFO_Start (Date on which the CFO that was in place during the Deal began his/her position as CFO)
• CFO_End (Date on which the CFO that was in place during the Deal left his/her position as CFO)

The merged dataset looks like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 DirectorID str26 DirectorName str128 CompanyName str74 RoleName str7 CompanyID int(DateStartRole DateEndRole) str33(HOCountryName Sector) double Deal_No int Deal_Announced str7(Acq_ID CFO_ID) int(CFO_Start CFO_End)
"323324" "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "Controller"                     "22923"    9862 11048 "United States"            "Leisure & Hotels"           .     . ""      ""           .     .
"323324" "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "Controller/Treasurer"           "22923"   11048 12174 "United States"            "Leisure & Hotels"           .     . ""      ""           .     .
"323324" "A Fitzhugh" "Southern Land Company LLC"                             "CFO"                            "1497521" 16376 17837 "United States"            ""                           .     . ""      ""           .     .
"323324" "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "CFO/Secretary/Treasurer"        "22923"   13402 16284 "United States"            "Leisure & Hotels"  1339168020 15641 "22923" "323324" 13402 16284
"323324" "A Fitzhugh" "Cosi Inc"                                              "Interim CFO"                    "2539052" 21173 22504 "United States"            "Leisure & Hotels"           .     . ""      ""           .     .
"323324" "A Fitzhugh" "JASON Foundation"                                      "Director - ED"                  "78144"   14976 21550 "United States"            ""                           .     . ""      ""           .     .
"323324" "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "CFO/Secretary/Treasurer"        "22923"   13402 16284 "United States"            "Leisure & Hotels"  1002966020 14733 "22923" "323324" 13402 16284
"323324" "A Fitzhugh" "O'CHARLEYS INC (De-listed 05/2012)"                    "Secretary/Treasurer/Controller" "22923"   12174 13402 "United States"            "Leisure & Hotels"           .     . ""      ""           .     .
"275461" "A Leighton" "Resna Industries Inc"                                  "VP/CFO"                         "562411"  10958 11870 "United States"            ""                           .     . ""      ""           .     .
"275461" "A Leighton" "SONIC SOLUTIONS (De-listed 02/2011)"                   "Vice President - Finance"       "28575"   12085 14245 "United States"            "Business Services"          .     . ""      ""           .     .
"275461" "A Leighton" "SONIC SOLUTIONS (De-listed 02/2011)"                   "Executive VP/CFO"               "28575"   16705 17587 "United States"            "Business Services"          .     . ""      ""           .     .
"275461" "A Leighton" "San Francisco Vikings Youth Soccer League"             "CFO"                            "562416"  18993 19724 "United States"            ""                           .     . ""      ""           .     .
"275461" "A Leighton" "Boston Consulting Group (BCG) Inc"                     "Consultant"                     "35643"      .n 22504 "United States"            "Business Services"          .     . ""      ""           .     .
"275461" "A Leighton" "San Francisco Vikings Youth Soccer League"             "Vice President"                 "562416"  18628 18993 "United States"            ""                           .     . ""      ""           .     .
"275461" "A Leighton" "SONIC SOLUTIONS (De-listed 02/2011)"                   "Executive VP/COO"               "28575"   17587 18676 "United States"            "Business Services"          .     . ""      ""           .     .
"275461" "A Leighton" "SONIC SOLUTIONS (De-listed 02/2011)"                   "Senior VP/CFO"                  "28575"   14245 16705 "United States"            "Business Services" 1481833020 16103 "28575" "275461" 14245 16705
"275461" "A Leighton" "San Francisco Vikings Youth Soccer League"             "Director - Non-Brd"             "562416"  19724 22504 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "Utility Service Affiliates (Perth Amboy) Inc"          "VP/Treasurer"                   "631182"     .n 16131 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "Utility Service Affiliates (Perth Amboy) Inc"          "VP/Treasurer"                   "631182"  16131 22504 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "Pinelands Water Co"                                    "VP/Treasurer"                   "632212"     .n 16131 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "Bayview Water Co"                                      "Treasurer"                      "631183"  16131 16071 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "MIDDLESEX WATER CO"                                    "VP/CFO/Controller"              "20638"   13270 16253 "United States"            "Utilities - Other"          .     . ""      ""           .     .
"342193" "A O'Connor" "White Marsh Environmental Systems Inc"                 "Treasurer"                      "631095"  16131 22504 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "Tidewater Utilities Inc"                               "Treasurer"                      "631094"     .n 16131 "United States"            "Utilities - Other"          .     . ""      ""           .     .
"342193" "A O'Connor" "Pinelands Wastewater Co"                               "VP/Treasurer"                   "631179"     .n 16131 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "Pinelands Wastewater Co"                               "VP/Treasurer"                   "631179"  16131 22504 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "MIDDLESEX WATER CO"                                    "Vice President"                 "20638"   12784 13270 "United States"            "Utilities - Other"          .     . ""      ""           .     .
"342193" "A O'Connor" "Deloitte LLP (Deloitte & Touche LLP prior to 12/2008)" "Various Positions"              "35734"    8766 10958 "United Kingdom - England" "Business Services"          .     . ""      ""           .     .
"342193" "A O'Connor" "TWIN LAKES INC"                                        "VP/Treasurer"                   "1205766"    .n 22504 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "Bayview Water Co"                                      "Treasurer"                      "631183"     .n 16131 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "MIDDLESEX WATER CO"                                    "VP/CFO/Treasurer"               "20638"   19783 21550 "United States"            "Utilities - Other"          .     . ""      ""           .     .
"342193" "A O'Connor" "Tidewater Utilities Inc"                               "Treasurer"                      "631094"  16131 21550 "United States"            "Utilities - Other"          .     . ""      ""           .     .
"342193" "A O'Connor" "White Marsh Environmental Systems Inc"                 "Treasurer"                      "631095"     .n 16131 "United States"            ""                           .     . ""      ""           .     .
"342193" "A O'Connor" "Tidewater Environmental Services Inc"                  "Treasurer"                      "631097"  16131 22504 "United States"            "Business Services"          .     . ""      ""           .     .
"342193" "A O'Connor" "Tidewater Environmental Services Inc"                  "Treasurer"                      "631097"     .n 16131 "United States"            "Business Services"          .     . ""      ""           .     .
end
format %tddd/nn/CCYY DateStartRole
format %tddd/nn/CCYY DateEndRole
format %tddd/nn/CCYY Deal_Announced
format %tddd/nn/CCYY CFO_Start
format %tddd/nn/CCYY CFO_End
format Deal_No %15.0g
compress

This dataset shows the employment history of managers as well as the deals of the respective companies at times when the manager was in place as CFO.

For example, the first director in the list is "A Fitzhugh", who has a total of 8 entries. For this Manager, there are two entries in the Deal_No variable. More specifically during his time as CFO at "O'CHARLEYS INC" from 09/10/1996 to 08/01/2004, the company acquired two other companies. The first deal was announced on 03/05/2000, the second on 28/10/2002.
The second manager in the list, "A Leighton", served from 1993 to 2011 in total (in different positions) and also managed one deal as CFO for "SONIC SOLUTIONS".

Now I've been trying for hours to find a way to create a list for each Deal consisting of those companies where the manager/CFO in place worked BEFORE the deal was announced.
The logic should be the following:
For each entry in Deal_No -> List all names (CompanyName) where the Managers End Date of a Role (DateEndRole) is BEFORE the Announcement Date (Deal_Announced) of the Deal (Deal_No) and where the Acq_ID is not the CompanyID.

Some additional context in case anyone is interested: For each deal in the Deal_No list, I want to find out if the manager has previously worked at a company that acquired another company (to determine M&A experience). To do this, I have a list of all M&A as well as the names of the associated acquirer in a third dataset. Unfortunately, there is no unique ID between the second and third dataset. Therefore I am using a string based match making approach programmed in Python to match the names of the companies where a manager has worked before a particular deal (the list I am currently trying in vain to create) with the names in my third dataset.

Any help would be very much appreciated.

Best

[Clyde Schechter]

I'm very confused by "For each entry in Deal_No -> List all names (CompanyName) where the CFO End Date (CFO_End) is BEFORE the Announcement Date (Deal_Announced) of the Deal (Deal_No) and where the Acq_ID is not the CompanyID." In the example data, CFO_End is always missing except for the observations where there is a deal. So CFO_End is going to be completely uninformative with respect to the CFO's employment at any place before or after. Moreover the condition CFO_End < Deal_Announced is never true in the example data.

Did you mean to refer to DateEndRole instead?

[Klaus Klausen]

Thanks for your answer Clyde. You are right, sorry for the typo. I am refering to DateEndRole, so:
For each entry in Deal_No -> List all names (CompanyName) where the Managers End Date of a Role (DateEndRole) is BEFORE the Announcement Date (Deal_Announced) of the Deal (Deal_No) and where the Acq_ID is not the CompanyID.
I will edit the original post

[Clyde Schechter]

OK. You actually made the task harder by merging the two data sets together. The first part of the solution here is to break them apart.

//  CREATE A SEPARATE FILE OF DIRECTOR HISTORIES
preserve
keep DirectorID-Sector
tempfile director_histories
save `director_histories'
restore

//  REDUCE THE FILE TO JUST DEALS
keep Deal_No-CFO_End
drop if missing(Deal_No)
rename CFO_ID DirectorID

//  MATCH EACH DEAL WITH ITS CFO'S PRIOR EMPLOYMENT HISTORY
rangejoin DateEndRole . Deal_Announced using `director_histories', by(DirectorID)
drop if Acq_ID == CompanyID // ELIMINATE PREVIOUS EMPLOYMENT AT SAME FIRM

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

[Klaus Klausen]

This seems to work perfect, thank you very much Clyde.