Contributor
Posts: 20

# Coding previous years of CEO turnover event

Hi,

I have CEO turover data like the following.

gvkey year  ceoturnover

A        1992       0

A         1993      1

B         2003      0

B         2004      0

B         2005       1

And I would like to code the year immediately preceding a CEO turnover like the following.

gvkey year  ceoturnover  pceoturnover

A        1992       0                  1

A         1993      1                  0

B         2003      0                  0

B         2004      0                   1

B         2005       1                 0

It will be very appreciative if you can advise how to code this problem in SAS,

Super User
Posts: 2,061

## Re: Coding previous years of CEO turnover event

[ Edited ]

data have;
input gvkey \$ year ceoturnover;
datalines;
A 1992 0
A 1993 1
B 2003 0
B 2004 0
B 2005 1
;

data want;
do _n_=1 by 1 until(last.gvkey);
set have;
by gvkey year;
if ceoturnover=1 then _m=_n_-1;
end;
do _n_=1 by 1 until(last.gvkey);
set have;
by gvkey year;
if _n_=_m then pceoturnover=1;
else pceoturnover=0;
output;
end;
drop _:;
run;

EDIT: I assumed your data is sorted by gvkey year as shown in your sample

Super User
Posts: 2,061

## Re: Coding previous years of CEO turnover event

[ Edited ]

Using proc sql:

data have;

input _gvkey \$ _year  _ceoturnover;

datalines;

A        1992       0

A         1993      1

B         2003      0

B         2004      0

B         2005      1

;

proc sql;

create table want  as

select a.* ,(b.year-a.year=1 and b.ceoturnover=1) as pceoturnover

from have a, have b

group by a.gvkey

having  b.year-a.year=1 or   max(a.year)=a.year and a.year=b.year

order by a.gvkey, a.year ;

quit;

PROC Star
Posts: 1,400

## Re: Coding previous years of CEO turnover event

[ Edited ]

If you have SAS/ETS do something like this

data have;
input gvkey\$ year ceoturnover;
datalines;
A 1992 0
A 1993 1
B 2003 0
B 2004 0
B 2005 1
;

proc expand data=have out=want method=none;
by gvkey;
id year;
convert ceoturnover=pceoturnover / transformout=(lead 1 setmiss 0);
run;
Super User
Posts: 10,850

## Re: Coding previous years of CEO turnover event

If I understood what you mean.

data have;
input gvkey \$ year ceoturnover;
datalines;
A 1992 0
A 1993 1
B 2003 0
B 2004 0
B 2005 1
;

data want;
merge have have(keep= gvkey  year ceoturnover
rename=( gvkey=_g year=_y ceoturnover=_c ) firstobs=2);
pceoturnover=0;
if gvkey=_g and year+1=_y then pceoturnover=_c;
drop _:;
run;
Contributor
Posts: 20

## Re: Coding previous years of CEO turnover event

Thank you so much to everybody who helped this programming question

I feel that our society has a positive social capital. Thanks again

Discussion stats
• 5 replies
• 237 views
• 2 likes
• 4 in conversation