CEO change and tenure

Solved
Occasional Contributor
Posts: 5

CEO change and tenure

[ Edited ]

Hi,

In the attached file, I've data on CEO change. The first three columns are what I have in the dataset, and the last three columns are what I want as output.

Input (what I got):

Column 1: GVKEY, or any other unique identifier.

Column 2: Fiscal Year - fyear as it is in the COMPUSTAT.

Column 3: Years in which CEO of my interest was in the board.

Output (what I intend to get):

Column 4: If CEO isn't in service (CEO=0), column 4 takes 0. If CEO is in service (CEO=1), then columns 4 carries his tenure, starting from the year of joining. Tenure will be same across the years. However, if CEO serves till the last year, column 04 will take 999.

Column 5: It will take the year of appointment as the value and carry it through the year of exit.

Column 6: Indicator variable 1, if CEO changes this year (CEO becomes 0 from 1).

I've similar data for CFO and want to get the same using a code. I highly appreciate your help.

Thanks.

Accepted Solutions
Solution
‎05-01-2016 01:13 AM
Super User
Posts: 10,766

Re: CEO change and tenure

```OK.Here. I have no time to TEST it.

data have;
infile cards expandtabs truncover;
input gvkey 	fyear	CEO;
cards;
11	2004	0
11	2005	0
11	2006	1
11	2007	1
11	2008	1
11	2009	1
11	2010	0
11	2011	0
11	2012	0
11	2013	1
11	2014	1
11	2015	1
22	2005	1
22	2006	1
22	2007	1
22	2008	1
22	2009	1
22	2010	1
22	2011	0
22	2012	0
22	2013	0
22	2014	0
22	2015	0
;
run;

data want;
tenure=0;
appoint_yr=0;

do until(last.CEO);
set have;
by gvkey CEO notsorted;
if CEO then tenure+1;
if first.CEO and CEO then appoint_yr=fyear;
end;

if CEO and last.gvkey  then tenure=999;

do until(last.CEO);
set have;
by gvkey CEO notsorted;
if first.CEO and not CEO and not first.gvkey then exit_yr=1;
else exit_yr=0;
if first.CEO and CEO then Entry_yr=1;
else Entry_yr=0;
output;
end;
run;

```

All Replies
Super User
Posts: 10,766

Re: CEO change and tenure

OK. Clear.
```data have;
infile cards expandtabs truncover;
input gvkey 	fyear	CEO;
cards;
11	2004	0
11	2005	0
11	2006	1
11	2007	1
11	2008	1
11	2009	1
11	2010	0
11	2011	0
11	2012	0
11	2013	1
11	2014	1
11	2015	1
22	2005	1
22	2006	1
22	2007	1
22	2008	1
22	2009	1
22	2010	1
22	2011	0
22	2012	0
22	2013	0
22	2014	0
22	2015	0
;
run;

data want;
tenure=0;
appoint_yr=0;

do until(last.CEO);
set have;
by gvkey CEO notsorted;
if CEO then tenure+1;
if first.CEO and CEO then appoint_yr=fyear;
end;

if CEO and fyear=2015 then tenure=999;

do until(last.CEO);
set have;
by gvkey CEO notsorted;
if first.CEO and not CEO and not first.gvkey then exit_yr=1;
else exit_yr=0;
output;
end;
run;

```
Occasional Contributor
Posts: 5

Re: CEO change and tenure

Hi Ksharp, I appreciate your effort. The code works great. However, I've the following concern:

1. For 999 code, 2015 is not necessarily the last year. It's basically the last year for which a firm's data is available. It could be 2014, or 2013, or could even be 2007 for example, which might be the case for missing or acquired firms.

2. My bad, I forgot to add a column, Entry_yr. Like exit_yr, entry_yr equals to 1 for the first year of appointment and 0 otherwise. For example, for the given data gvkey 11 should have 1 in 2006 and in 2013, 0 otherwise.

3. I've similar data for CFO and other employees (in additional columns). I want to use the same code, without copying it several times.

Thanks again.

Solution
‎05-01-2016 01:13 AM
Super User
Posts: 10,766

Re: CEO change and tenure

```OK.Here. I have no time to TEST it.

data have;
infile cards expandtabs truncover;
input gvkey 	fyear	CEO;
cards;
11	2004	0
11	2005	0
11	2006	1
11	2007	1
11	2008	1
11	2009	1
11	2010	0
11	2011	0
11	2012	0
11	2013	1
11	2014	1
11	2015	1
22	2005	1
22	2006	1
22	2007	1
22	2008	1
22	2009	1
22	2010	1
22	2011	0
22	2012	0
22	2013	0
22	2014	0
22	2015	0
;
run;

data want;
tenure=0;
appoint_yr=0;

do until(last.CEO);
set have;
by gvkey CEO notsorted;
if CEO then tenure+1;
if first.CEO and CEO then appoint_yr=fyear;
end;

if CEO and last.gvkey  then tenure=999;

do until(last.CEO);
set have;
by gvkey CEO notsorted;
if first.CEO and not CEO and not first.gvkey then exit_yr=1;
else exit_yr=0;
if first.CEO and CEO then Entry_yr=1;
else Entry_yr=0;
output;
end;
run;

```
Occasional Contributor
Posts: 5

Re: CEO change and tenure

Hi Ksharp, it works great. Can you generalize the code so that I can use it for multiple employees, say for CFO and COO, without copying it several times?

Super User
Posts: 23,683

Re: CEO change and tenure

Since it depends on variable order you'd be running it once for each position.

Please try and extend it and post back if you have issues.

Super User
Posts: 10,766

Re: CEO change and tenure

```Use a macro variable to replace CEO . Like:

%let position=CEO;

.......
if first.&position ...........

And change this macro variable to apply to others . Like:
%let position=CFO;

```
🔒 This topic is solved and locked.