Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Ceo Tenure - Compustat: negative tenure observations

    Hello all,

    I am working with Executive characteristics. Using Execucomp data from 1992 to 2025. I need to generate CEO_Tenure (CEO tenure in years at each financial statement date; which is FYEAR here) However, when doing so I run into negative CEO_Tenure observations. I have already done some manual inspecting and what I have noticed is that even though firm-level observations start in 1997, the becameceo variable mentions the CEO started in for example 2003. I looked some observations up online and sometimes the becameceo variable is completely wrong on when the CEO actually became CEO

    Here an example:
    fyear becameceo exec_fullname coname ceo_tenure
    2004 01/08/2012 Hugh Grant MONSANTO CO -8

    But when I look online, I find that Hugh Grant started his position as CEO at the company in 2003

    Is this a known problem with the becameceo variable from ExecuComp?

    the code I ran was

    Code:
    gen ceo_tenure = fyear - year(becameceo)
    Please help me out

    I'm using DD/MM/YYYY as date format


    See dataex ouput:

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str6 gvkey double fyear str28 coname str5 execid str50 exec_fullname long becameceo str3 ceoann float ceo_tenure
"001004" 1996 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   0
"001004" 1997 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   1
"001004" 1998 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   2
"001004" 1999 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   3
"001004" 2000 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   4
"001004" 2001 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   5
"001004" 2002 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   6
"001004" 2003 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   7
"001004" 2004 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   8
"001004" 2005 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   9
"001004" 2006 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  10
"001004" 2007 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  11
"001004" 2008 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  12
"001004" 2009 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  13
"001004" 2010 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  14
"001004" 2011 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  15
"001004" 2012 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  16
"001004" 2013 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  17
"001004" 2018 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   0
"001004" 2019 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   1
"001004" 2020 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   2
"001004" 2021 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   3
"001004" 2022 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   4
"001004" 2023 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   5
"001004" 2024 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   6
"001013" 2004 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   1
"001013" 2005 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   2
"001013" 2006 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   3
"001013" 2007 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   4
"001013" 2008 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   5
"001013" 2009 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   6
"001013" 2010 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   7
"001034" 1994 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   0
"001034" 1995 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   1
"001034" 1996 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   2
"001034" 1997 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   3
"001034" 1998 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   4
"001034" 2000 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   0
"001034" 2001 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   1
"001034" 2002 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   2
"001034" 2003 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   3
"001034" 2004 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   4
"001034" 2005 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   5
"001045" 1992 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"   7
"001045" 1993 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"   8
"001045" 1994 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"   9
"001045" 1995 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"  10
"001045" 1996 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"  11
"001045" 1997 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"  12
"001045" 1998 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   0
"001045" 1999 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   1
"001045" 2000 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   2
"001045" 2001 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   3
"001045" 2002 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   4
"001045" 2003 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   0
"001045" 2004 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   1
"001045" 2005 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   2
"001045" 2006 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   3
"001045" 2007 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   4
"001045" 2008 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   5
"001045" 2009 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   6
"001045" 2010 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   7
"001045" 2011 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   8
"001045" 2022 "AMERICAN AIRLINES GROUP INC" "46193" "Robert D. Isom, Jr."      22735 "CEO"   0
"001045" 2023 "AMERICAN AIRLINES GROUP INC" "46193" "Robert D. Isom, Jr."      22735 "CEO"   1
"001045" 2024 "AMERICAN AIRLINES GROUP INC" "46193" "Robert D. Isom, Jr."      22735 "CEO"   2
"001056" 1997 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO" -10
"001056" 1998 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO"  -9
"001056" 2007 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO"   0
"001056" 2008 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO"   1
"001056" 2009 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO"   2
"001072" 2001 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   0
"001072" 2002 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   1
"001072" 2003 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   2
"001072" 2004 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   3
"001072" 2005 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   4
"001072" 2006 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   5
"001072" 2007 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   6
"001072" 2008 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   7
"001072" 2009 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   8
"001072" 2010 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   9
"001072" 2011 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"  10
"001072" 2012 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"  11
"001072" 2015 "AVX CORP"                    "49833" "John Sarvis"              20179 "CEO"   0
"001072" 2016 "AVX CORP"                    "49833" "John Sarvis"              20179 "CEO"   1
"001072" 2017 "AVX CORP"                    "49833" "John Sarvis"              20179 "CEO"   2
"001072" 2018 "AVX CORP"                    "49833" "John Sarvis"              20179 "CEO"   3
"001075" 1993 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   3
"001075" 1994 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   4
"001075" 1995 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   5
"001075" 1996 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   6
"001075" 1997 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   7
"001075" 1998 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   8
"001075" 1999 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   0
"001075" 2000 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   1
"001075" 2001 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   2
"001075" 2002 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   3
"001075" 2003 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   4
"001075" 2004 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   5
"001075" 2005 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   6
end
format %tdDD/NN/CCYY becameceo
    Last edited by Hessel Kreeft; 07 May 2026, 07:50.
    Tags: None
  • #2
    Hessel Kreeft the becameceo variable in these data is notoriously "noisy" because S&P/Execucomp often updates this field in later filings but doesn't always retroactively clean it in the historical records provided to WRDS. In your Hugh Grant example, the 2012 date likely refers to a contract renewal or a specific reappointment that overwritten the original 2003 start date in the db...To fix & generate a robust ceo_tenure variable w/ "minimum-per-executive-firm" try identifying the earliest reported becameceo date for each executive at each firm and use that as the constant _Global Start Date_for that CEOs tenure. nb. If becameceo is missing entirely (common for older CEOs who started before 92') then using the first fyear they appear in your dataset could be a proxy for "Tenure in the dataset." I still see some unresolved negative values after this , I suggest you check for Executive ID reuse if this doesnt help.


    Code:
    clear
input str6 gvkey double fyear str28 coname str5 execid str50 exec_fullname long becameceo str3 ceoann float ceo_tenure
"001004" 1996 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   0
"001004" 1997 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   1
"001004" 1998 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   2
"001004" 1999 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   3
"001004" 2000 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   4
"001004" 2001 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   5
"001004" 2002 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   6
"001004" 2003 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   7
"001004" 2004 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   8
"001004" 2005 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"   9
"001004" 2006 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  10
"001004" 2007 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  11
"001004" 2008 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  12
"001004" 2009 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  13
"001004" 2010 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  14
"001004" 2011 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  15
"001004" 2012 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  16
"001004" 2013 "AAR CORP"                    "09249" "David P. Storch"          13431 "CEO"  17
"001004" 2018 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   0
"001004" 2019 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   1
"001004" 2020 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   2
"001004" 2021 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   3
"001004" 2022 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   4
"001004" 2023 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   5
"001004" 2024 "AAR CORP"                    "48195" "John McClain Holmes, III" 21336 "CEO"   6
"001013" 2004 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   1
"001013" 2005 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   2
"001013" 2006 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   3
"001013" 2007 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   4
"001013" 2008 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   5
"001013" 2009 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   6
"001013" 2010 "ADC TELECOMMUNICATIONS INC"  "10203" "Robert E. Switz"          15918 "CEO"   7
"001034" 1994 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   0
"001034" 1995 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   1
"001034" 1996 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   2
"001034" 1997 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   3
"001034" 1998 "ALPHARMA INC  -CL A"         "06390" "Einar W. Sissner"         12570 "CEO"   4
"001034" 2000 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   0
"001034" 2001 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   1
"001034" 2002 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   2
"001034" 2003 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   3
"001034" 2004 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   4
"001034" 2005 "ALPHARMA INC  -CL A"         "11170" "Ingrid Wiik"              14610 "CEO"   5
"001045" 1992 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"   7
"001045" 1993 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"   8
"001045" 1994 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"   9
"001045" 1995 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"  10
"001045" 1996 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"  11
"001045" 1997 "AMERICAN AIRLINES GROUP INC" "00058" "Robert L. Crandall"        9191 "CEO"  12
"001045" 1998 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   0
"001045" 1999 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   1
"001045" 2000 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   2
"001045" 2001 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   3
"001045" 2002 "AMERICAN AIRLINES GROUP INC" "03661" "Donald J. Carty"          14019 "CEO"   4
"001045" 2003 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   0
"001045" 2004 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   1
"001045" 2005 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   2
"001045" 2006 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   3
"001045" 2007 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   4
"001045" 2008 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   5
"001045" 2009 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   6
"001045" 2010 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   7
"001045" 2011 "AMERICAN AIRLINES GROUP INC" "14591" "Gerard J. Arpey"          15820 "CEO"   8
"001045" 2022 "AMERICAN AIRLINES GROUP INC" "46193" "Robert D. Isom, Jr."      22735 "CEO"   0
"001045" 2023 "AMERICAN AIRLINES GROUP INC" "46193" "Robert D. Isom, Jr."      22735 "CEO"   1
"001045" 2024 "AMERICAN AIRLINES GROUP INC" "46193" "Robert D. Isom, Jr."      22735 "CEO"   2
"001056" 1997 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO" -10
"001056" 1998 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO"  -9
"001056" 2007 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO"   0
"001056" 2008 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO"   1
"001056" 2009 "AEROFLEX INC"                "19861" "Leonard Borow"            17440 "CEO"   2
"001072" 2001 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   0
"001072" 2002 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   1
"001072" 2003 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   2
"001072" 2004 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   3
"001072" 2005 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   4
"001072" 2006 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   5
"001072" 2007 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   6
"001072" 2008 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   7
"001072" 2009 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   8
"001072" 2010 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"   9
"001072" 2011 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"  10
"001072" 2012 "AVX CORP"                    "23168" "John S. Gilbertson"       15157 "CEO"  11
"001072" 2015 "AVX CORP"                    "49833" "John Sarvis"              20179 "CEO"   0
"001072" 2016 "AVX CORP"                    "49833" "John Sarvis"              20179 "CEO"   1
"001072" 2017 "AVX CORP"                    "49833" "John Sarvis"              20179 "CEO"   2
"001072" 2018 "AVX CORP"                    "49833" "John Sarvis"              20179 "CEO"   3
"001075" 1993 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   3
"001075" 1994 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   4
"001075" 1995 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   5
"001075" 1996 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   6
"001075" 1997 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   7
"001075" 1998 "PINNACLE WEST CAPITAL CORP"  "00759" "Richard Snell"            10993 "CEO"   8
"001075" 1999 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   0
"001075" 2000 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   1
"001075" 2001 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   2
"001075" 2002 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   3
"001075" 2003 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   4
"001075" 2004 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   5
"001075" 2005 "PINNACLE WEST CAPITAL CORP"  "10739" "William Joseph Post, CPA" 14276 "CEO"   6
end
format %tdDD/NN/CCYY becameceo

**Find the earliest 'becameceo' date reported for each executive in each firm
bysort gvkey execid: egen earliest_start = min(becameceo) // Find earliest start date across all records for this CEO-Firm pair
format earliest_start %td // Apply daily date format for readability

**Handle cases where becameceo might be missing but they are listed as CEO
  *We can proxy the start year as the minimum fyear the exec appears in the data
  bys gvkey execid: egen first_fyear_seen = min(fyear) // Identify the first  fiscal year the CEO appears in the data

**Create a "Cleaned" Start Year
    *Priority: Earliest becameceo, then fallback to first fiscal year seen
g start_year = year(earliest_start) // Extract the year from the earliest date   found
replace start_year = first_fyear_seen if mi(start_year) // Fallback to first  fyear appearance if date is missing

**Generate Tenure
g ceo_tenure_clean = fyear - start_year // Calculate tenure in years by     subtracting start year from fiscal year
**validation check
list fyear becameceo earliest_start ceo_tenure_clean if ceo_tenure_clean < 0 
//   identify any remaining negative values
    Eric A. Booth | Senior Director of Research | Far Harbor | Austin TX

    Comment

    • #3
      @eric_a_booth thank you for the response and the help. There is certainly a decrease in negative values.

      After the code you provided I used:
      Code:
      replace start_year = first_fyear_seen if start_year > first_fyear_seen
      This gave me zero negative values, however, I just wanted to follow up and make sure that me doing so is okay and I do not mess up my datasets validity this way.

      Comment

      Previous Next
      Working...
      X