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,
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
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;
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;
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;
Thank you so much to everybody who helped this programming question
I feel that our society has a positive social capital. Thanks again
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.