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