How to create a progressive variable (CEO tenure)

[RUBY JULY]

Hi everyone,

I want to create CEO’s tenure based on “Date become CEO” and “Date left as CEO”. My problem is how to create a progressive tenure variable?

gen tenure = (BECAMECEO – LEFTOFC)/365

next, I want to create a progressive tenure variable:

For example: firm: 1004 in 2002 has CEO with a tenure of 21.65 and CEO’s tenure will increase in 2003 = 21.65 + 1 = 22.65, 2004 = 23.65, and following years, so how I can code CEO’s tenure after 2002? (P/S: to verify CEOs of a firm, I used the variables: EXECID and CEOANN). Thank you.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(gvkey fyear) str50 EXEC_FULLNAME str4 CEOANN double AGE str6 EXECID long(BECAMECEO LEFTOFC) str6 GENDER float tenure
1004 2002 "David P. Storch" "CEO" 50 "09249" 13431 21335 "MALE" 21.654795
1004 2003 "David P. Storch" "CEO" 51 "09249" 13431 21335 "MALE" 21.654795
1004 2004 "David P. Storch" "CEO" 52 "09249" 13431 21335 "MALE" 21.654795
1004 2005 "David P. Storch" "CEO" 53 "09249" 13431 21335 "MALE" 21.654795
1004 2006 "David P. Storch" "CEO" 54 "09249" 13431 21335 "MALE" 21.654795
1004 2007 "David P. Storch" "CEO" 55 "09249" 13431 21335 "MALE" 21.654795
1004 2008 "David P. Storch" "CEO" 56 "09249" 13431 21335 "MALE" 21.654795
1004 2009 "David P. Storch" "CEO" 57 "09249" 13431 21335 "MALE" 21.654795
1004 2010 "David P. Storch" "CEO" 58 "09249" 13431 21335 "MALE" 21.654795
1004 2011 "David P. Storch" "CEO" 59 "09249" 13431 21335 "MALE" 21.654795
1004 2012 "David P. Storch" "CEO" 60 "09249" 13431 21335 "MALE" 21.654795
1004 2013 "David P. Storch" "CEO" 61 "09249" 13431 21335 "MALE" 21.654795
1004 2014 "David P. Storch" "CEO" 62 "09249" 13431 21335 "MALE" 21.654795
1004 2015 "David P. Storch" "CEO" 63 "09249" 13431 21335 "MALE" 21.654795
1004 2016 "David P. Storch" "CEO" 64 "09249" 13431 21335 "MALE" 21.654795
1034 2002 "Jeffrey E. Smith" "" . "06392" 11688 12570 "MALE" 2.416438
1034 2003 "Ingrid Wiik" "CEO" 59 "11170" 14610 16982 "FEMALE" 6.49863
1034 2004 "Ingrid Wiik" "CEO" 60 "11170" 14610 16982 "FEMALE" 6.49863
1034 2005 "Ingrid Wiik" "CEO" 61 "11170" 14610 16982 "FEMALE" 6.49863
1034 2006 "Ingrid Wiik" "" . "11170" 14610 16982 "FEMALE" 6.49863
1038 2002 "" "" . "" . . "" .
1045 2001 "Robert W. Baker" "" . "03660" . . "MALE" .
1045 2002 "Robert W. Baker" "" . "03660" . . "MALE" .
1045 2003 "Donald J. Carty" "" . "03661" 14019 15819 "MALE" 4.931507
1045 2004 "Gerard J. Arpey" "CEO" 46 "14591" 15820 18959 "MALE" 8.6
1045 2005 "Gerard J. Arpey" "CEO" 47 "14591" 15820 18959 "MALE" 8.6
1045 2006 "Gerard J. Arpey" "CEO" 48 "14591" 15820 18959 "MALE" 8.6
1045 2007 "Gerard J. Arpey" "CEO" 48 "14591" 15820 18959 "MALE" 8.6
1045 2008 "Gerard J. Arpey" "CEO" 49 "14591" 15820 18959 "MALE" 8.6
1045 2009 "Gerard J. Arpey" "CEO" 50 "14591" 15820 18959 "MALE" 8.6
1045 2010 "Gerard J. Arpey" "CEO" 51 "14591" 15820 18959 "MALE" 8.6
1045 2011 "Gerard J. Arpey" "CEO" 52 "14591" 15820 18959 "MALE" 8.6
1045 2014 "Derek J. Kerr" "" 49 "46189" . . "MALE" .
1045 2015 "Derek J. Kerr" "" 50 "46189" . . "MALE" .
1045 2016 "Derek J. Kerr" "" 51 "46189" . . "MALE" .
end
format %td BECAMECEO
format %td LEFTOFC
[/CODE]

[George Ford]

for starters, try

bys gvkey: g tenure2 = fyear-year(BECAMECEO)

[Clyde Schechter]

For example: firm: 1004 in 2002 has CEO with a tenure of 21.65 and CEO’s tenure will increase in 2003 = 21.65 + 1 = 22.65, 2004 = 23.65

At least in terms of the ordinary use of the term tenure, as I understand it, this sentence makes no sense. In firm 1004, the CEO became CEO on 9 Oct 1996 and left office on 31 May 2018. So his total tenure is, indeed, 21.65... years. But in FY 2002, his tenure up to that point is just from 9 Oct 1996 to some date in 2001 or 2002 (depending on when that firm's fiscal year ends.) If we assume this firm's fiscal years end on 31 October, then at the end of FY 2022, his tenure will be from 9 Oct 1996 to 31 Oct 2002, which is about 6.06 years. It is from this kind of starting point that the progressive tenure should be calculated. If you just start at 22.65 and count up from there you will, in all but the first years, be attributing a progressive tenure that is longer than the total tenure!

To get this accurate, you really do need to know the date on which each firm's FY ends. If that is not available, here is how you would do it by just imputing a fiscal year end date of 30 June to all firms:

gen progresive_tenure = (mdy(6, 30, fyear) - BECAMECEO)/365.25

//  NOTE: IF YOU HAVE VERSION 17 YOU CAN CODE THIS MORE TRANSPARENTLY AS:
gen progressive_tenure = datediff_frac(BECAMECEO, mdy(6, 30, fyear), "y")

[George Ford]

I doubt the decimals will matter much, except maybe for really short tenures, and maybe not even then. But Clyde's approach is a good one and is one line of code.

[RUBY JULY]

Dear George and Clyde,

Thank you very much for your support. I can create the tenure variable now. Again, thanks both of you.

Best regards,

Ruby