Coding previous years of CEO turnover event

[joon1]

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,

[novinosrin]

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

[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;

[PeterClemmensen]

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;

[Ksharp]

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;

[joon1]

Thank you so much to everybody who helped this programming question

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