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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.