Hi Community
I have the following dataset:
ID year event
1 2014 0
1 2015 0
1 2016 1
2 2014 0
2 2015 1
2 2016 1
3 2014 1
3 2015 1
3 2016 1
I need to create an indicator that shows me the year the event happend, even for the years that the event was 0, as follows:
ID year event year_event
1 2014 0 2016
1 2015 0 2016
1 2016 1 2016
2 2014 0 2015
2 2015 1 2015
2 2016 1 2015
3 2014 1 2014
3 2015 1 2014
3 2016 1 2014
Any help is highly appreciated. Sorry if it is too simple.
Thanks!
UNTESTED CODE
data first;
set have;
prev_event=lag(event);
prev_id=lag(id);
if id=prev_id and event=1 and prev_event=0 then output;
run;
data want;
merge have first(rename=(year=year_event) drop=prev_event prev_id);
by id;
run;
Assumes data is sorted by ID and year.
UNTESTED CODE
data first;
set have;
prev_event=lag(event);
prev_id=lag(id);
if id=prev_id and event=1 and prev_event=0 then output;
run;
data want;
merge have first(rename=(year=year_event) drop=prev_event prev_id);
by id;
run;
Assumes data is sorted by ID and year.
TESTED CODE:
data count(rename= Year=Year_event);
set records;
by ID;
if first.ID then count=0;
if event eq 1 then count+1;
if count eq 1 then output;
drop event;
run;
data result;
merge records count;
by ID;
drop count;
run;
proc print data=result;
run;
data have;
input ID year event ;
cards;
1 2014 0
1 2015 0
1 2016 1
2 2014 0
2 2015 1
2 2016 1
3 2014 1
3 2015 1
3 2016 1
;
run;
data want;
do until(last.id);
set have;
by id;
if event and not found then do;new_year=year;found=1;end;
end;
do until(last.id);
set have;
by id;
output;
end;
drop found;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.