Creating dummy variable to indicate whether person joined company after or before his peers

[Matthias Brueggemann]

Hi together,

I have a question regarding the following table:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str50 EXEC_FULLNAME str3 CEO_CFO double YEAR str50 CONAME
"Ira A. Eichner"       "CEO" 1994 "AAR CORP"
"Timothy J. Romenesko" "CFO" 1994 "AAR CORP"
"Ira A. Eichner"       "CEO" 1995 "AAR CORP"
"David P. Storch"      "CEO" 1996 "AAR CORP"
"David P. Storch"      "CEO" 1997 "AAR CORP"
"David P. Storch"      "CEO" 1998 "AAR CORP"
"David P. Storch"      "CEO" 1999 "AAR CORP"
"Michael J. Sharp"     "CFO" 1999 "AAR CORP"
"David P. Storch"      "CEO" 2000 "AAR CORP"
"Timothy J. Romenesko" "CFO" 2000 "AAR CORP"
"Michael J. Sharp"     "CFO" 2000 "AAR CORP"
"David P. Storch"      "CEO" 2001 "AAR CORP"
"Timothy J. Romenesko" "CFO" 2001 "AAR CORP"
"David P. Storch"      "CEO" 2002 "AAR CORP"
"Timothy J. Romenesko" "CFO" 2002 "AAR CORP"
"Michael J. Sharp"     "CFO" 2002 "AAR CORP"
"David P. Storch"      "CEO" 2003 "AAR CORP"
"Timothy J. Romenesko" "CFO" 2003 "AAR CORP"
"David P. Storch"      "CEO" 2004 "AAR CORP"
"Timothy J. Romenesko" "CFO" 2004 "AAR CORP"
end

I want to generate a dummy variable named "co-option" which takes the value 1 if a CFO joined the company after the current CEO and takes the value 0 if a CFO joined the company before or in the same year as the current CEO. CFOs are marked as "CFO" in the column "CEO_CFO" whereas CEOs are indicated by a "CEO" in the column "CEO_CFO". The date at which the respective CEO or CFO entered the company is the year with the first entry in the table for this person.

An example:
The first entry for Ira A. Eichner and Timothy J. Romenesko in the table is the year 1994, which means that they both joined AAR Corp in the same year, hence the value would be a 0 in the column "co-option" for Timothy J. Romenesko.
In 2000, Michael J. Sharp joined AAR Corp as CFO, yet David P. Storch had already been in office as CEO before him since 1996. Hence, the value would be a 1 in the column "co-option" for Michael J. Sharp.
For the respective CEO there should be no entry in the column "co-option", only CFOs should be marked with either 0 or 1.

I hope I was able to explain my problem appropriately.

Thank you in advance for your comments.

Best

Matthias

[cu dao huy]

Hi,

I'm not so sure your problem, but here is my try to the keep CEO and CFO with the first year entered to the company:

ren *, lower
bys exec_fullname: egen minyear = min(year)
drop if year != minyear

the result will contain 4 persons, 2 CEO and 2 CFO but if we compare year of each CEO with each CFO we will have 4 comparison, is that right?

[Matthias Brueggemann]

Hi cu,

your comment was already very helpful. I have now obtained a table which lists the year when the respective person entered the company.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str50(exec_fullname coname) str3 ceo_cfo float minyear
"Dennis E. Foster"       "360 COMMUNICATIONS CO" "CEO" 1995
"Eric A. Benhamou"       "3COM CORP"             "CEO" 1993
"Michael E. Rescoe"      "3COM CORP"             "CFO" 2000
"Bruce L. Claflin"       "3COM CORP"             "CEO" 2001
"Mark Slaven"            "3COM CORP"             "CFO" 2002
"Donald M. Halsted, III" "3COM CORP"             "CFO" 2004
"Edgar Masri"            "3COM CORP"             "CEO" 2006
"Yu-Lang Mao"            "3COM CORP"             "CEO" 2008
"Damon J. Gregoire"      "3D SYSTEMS CORP"       "CFO" 2007
"Abraham N. Reichental"  "3D SYSTEMS CORP"       "CEO" 2007
"David Robert Styka"     "3D SYSTEMS CORP"       "CFO" 2015
"Vyomesh I. Joshi"       "3D SYSTEMS CORP"       "CEO" 2016
"Livio D. DeSimone"      "3M CO"                 "CEO" 1992
"W. James McNerney, Jr." "3M CO"                 "CEO" 2001
"Patrick D. Campbell"    "3M CO"                 "CFO" 2003
"David W. Meline"        "3M CO"                 "CFO" 2011
"Inge G. Thulin, M.B.A." "3M CO"                 "CEO" 2012
"Nicholas C. Gangestad"  "3M CO"                 "CFO" 2014
"Michael F. Roman"       "3M CO"                 "CEO" 2018
"Alfred R. Kahn"         "4LICENSING CORP"       "CEO" 1999
end

Now my goal is using this information to obtain a command which helps me to create the variable "co-option" in my original table:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str50(EXEC_FULLNAME CONAME) double YEAR str3 CEO_CFO
"Dennis E. Foster"       "360 COMMUNICATIONS CO" 1995 "CEO"
"Dennis E. Foster"       "360 COMMUNICATIONS CO" 1996 "CEO"
"Dennis E. Foster"       "360 COMMUNICATIONS CO" 1997 "CEO"
"Eric A. Benhamou"       "3COM CORP"             1993 "CEO"
"Eric A. Benhamou"       "3COM CORP"             1994 "CEO"
"Eric A. Benhamou"       "3COM CORP"             1995 "CEO"
"Eric A. Benhamou"       "3COM CORP"             1996 "CEO"
"Eric A. Benhamou"       "3COM CORP"             1997 "CEO"
"Eric A. Benhamou"       "3COM CORP"             1998 "CEO"
"Eric A. Benhamou"       "3COM CORP"             1999 "CEO"
"Eric A. Benhamou"       "3COM CORP"             2000 "CEO"
"Michael E. Rescoe"      "3COM CORP"             2000 "CFO"
"Bruce L. Claflin"       "3COM CORP"             2001 "CEO"
"Michael E. Rescoe"      "3COM CORP"             2001 "CFO"
"Bruce L. Claflin"       "3COM CORP"             2002 "CEO"
"Mark Slaven"            "3COM CORP"             2002 "CFO"
"Bruce L. Claflin"       "3COM CORP"             2003 "CEO"
"Mark Slaven"            "3COM CORP"             2003 "CFO"
"Bruce L. Claflin"       "3COM CORP"             2004 "CEO"
"Donald M. Halsted, III" "3COM CORP"             2004 "CFO"
end

Example:
Michael E. Rescoe is the first CFO who is mentioned in the table. He entered 3M in 2000, when Eric A. Benhamou had already been CEO since 1993. Therefore, Michael Rescoe would be given the value "1" in the column co-option for the year 2000. Yet, in 2001, Bruce L. Claflin entered 3M as CEO. Therefore, for the year 2001, Micheal E. Rescoe would receive the value "0" in the column "co-option".