DATA Step, Macro, Functions and more

Coding previous years of CEO turnover event

Reply
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,

 

PROC Star
Posts: 1,356

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

PROC Star
Posts: 1,356

Re: Coding previous years of CEO turnover event

[ Edited ]
Posted in reply to novinosrin

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,190

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,623

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

Ask a Question
Discussion stats
  • 5 replies
  • 206 views
  • 2 likes
  • 4 in conversation