Hi,
my data consists of 2 datasets.
Dataset A contains CEO employment data for a list of companies between 2007 to today. Each company and CEO is assigned an ID. For each CEO there is data on when he/she joined and left a specific position described in the variable Role. A person on this list has always holds the CEO position but may also hold additional positions (e.g. Chairman, President etc.)
Dataset B contains the date of a specific event that took place for each of these companies
Goal: I would like to merge datasets A and B by company so that for every entry (event) in B I have information on which CEO was in place around the event.
I am using the following command to achieve this goal:
The command is doing what it's supposed to do.
However, there is an issue with how the data in dataset A are presented.
For example, the CEO "David Biegler" was in place from the 2nd of November 2012 to the 3rd of March 2014 with the Role "Chairman/President/CEO". As of the 3rd of March 2014, "David Biegler" stepped down as the company's President but still was in the CEO position until the 5th of December 2014. This is represented in a separate entry.
In fact, Biegler was continuously CEO of the company from 02 November 2012 to 05 December 2014 before "John Bonn" took over.
Apparently, Biegler was reassigned as CEO from the 04th of March 2018 to the 17th of September 2018.
So the continuous CEO tenures of Biegler at that company would be: 2nd of November 2012 to 3rd of March 2014 and 04th of March 2018 to the 17th of September 2018
Using the rangejoin command above won't consider that each entry in dataset A may only represent a part of the overall CEO tenure which will ultimately lead to wrong tenure calculations.
My approach to account for this issue:
Unfortunately, cases in which the CEO is reassigned won't be correctly captured by this approach. That is, instead of two separate tenures I get one continuous in the case of Biegler, i.e. from 2nd of November 2012 to 17th of September 2018.
I would be happy to receive some help on how to correctly handle this issue.
Kind regards
my data consists of 2 datasets.
Dataset A contains CEO employment data for a list of companies between 2007 to today. Each company and CEO is assigned an ID. For each CEO there is data on when he/she joined and left a specific position described in the variable Role. A person on this list has always holds the CEO position but may also hold additional positions (e.g. Chairman, President etc.)
Code:
clear input long(Company_ID CEO_ID) str48 CEO_Name str114 Company_Name str85 Role int(DateStartRole DateEndRole) str12 Company_CUSIP 1901853 56607 "David Biegler" "SOUTHCROSS ENERGY PARTNERS LP (De-listed 02/2019)" "Chairman/President/CEO" 19299 19785 "US84130C1009" 1901853 56607 "David Biegler" "SOUTHCROSS ENERGY PARTNERS LP (De-listed 02/2019)" "Chairman/CEO" 19785 20062 "US84130C1009" 1901853 1377796 "John Bonn" "SOUTHCROSS ENERGY PARTNERS LP (De-listed 02/2019)" "President/CEO" 20062 20825 "US84130C1009" 1901853 141265 "Bruce Williamson" "SOUTHCROSS ENERGY PARTNERS LP (De-listed 02/2019)" "Chairman/President/CEO" 20825 21412 "US84130C1009" 1901853 56607 "David Biegler" "SOUTHCROSS ENERGY PARTNERS LP (De-listed 02/2019)" "Interim Chairman/Interim CEO/Interim President" 21247 21412 "US84130C1009" 1901853 56607 "David Biegler" "SOUTHCROSS ENERGY PARTNERS LP (De-listed 02/2019)" "Chairman/President/CEO" 21412 21444 "US84130C1009" 1901853 27558 "Jay Swent III" "SOUTHCROSS ENERGY PARTNERS LP (De-listed 02/2019)" "Chairman/President/CEO" 21444 21608 "US84130C1009" end format %tdDD.NN.CCYY DateStartRole format %tdDD.NN.CCYY DateEndRole
Dataset B contains the date of a specific event that took place for each of these companies
Code:
clear input str7 Company_ID str10 Event_Date str12 Company_CUSIP "1901853" "10.01.2013" "US84130C1009" "1901853" "02.03.2014" "US84130C1009" end
Goal: I would like to merge datasets A and B by company so that for every entry (event) in B I have information on which CEO was in place around the event.
I am using the following command to achieve this goal:
rangejoin EventDate DateStartRole DateEndRole using tempsave, by(Company_CUSIP)
However, there is an issue with how the data in dataset A are presented.
For example, the CEO "David Biegler" was in place from the 2nd of November 2012 to the 3rd of March 2014 with the Role "Chairman/President/CEO". As of the 3rd of March 2014, "David Biegler" stepped down as the company's President but still was in the CEO position until the 5th of December 2014. This is represented in a separate entry.
In fact, Biegler was continuously CEO of the company from 02 November 2012 to 05 December 2014 before "John Bonn" took over.
Apparently, Biegler was reassigned as CEO from the 04th of March 2018 to the 17th of September 2018.
So the continuous CEO tenures of Biegler at that company would be: 2nd of November 2012 to 3rd of March 2014 and 04th of March 2018 to the 17th of September 2018
Using the rangejoin command above won't consider that each entry in dataset A may only represent a part of the overall CEO tenure which will ultimately lead to wrong tenure calculations.
My approach to account for this issue:
Code:
bysort Company_ID CEO_ID: egen DateStartRole_MIN = min(DateStartRole) format DateStartRole_MIN %tdDD.NN.CCYY bysort Company_ID CEO_ID: egen DateEndRole_MAX = max(DateEndRole) format DateEndRole_MAX %tdDD.NN.CCYY
I would be happy to receive some help on how to correctly handle this issue.
Kind regards
