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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.