Hi all, I have a long dataset (multiple rows per person) and a binary outcome that can change over time.
I would like to be able to drop all of the observations for a person after their FIRST outcome (ie, no
rows for anyone after event=1 for the first time.
The trouble I've run into is the different patterns of events. For example: person 1 has an event,
and then the next row they do NOT have an event - I would want to drop this row AFTER the first event.
Same for person 2 and 3 as well where they have more rows after their first event=1 that may be event=0
or event=1. I was trying to code for something of an indicator that is a sum of the events to flag when
the FIRST time event=1 happens but ran into some trouble with the logic and making something that works
for the possible patterns in the data.
Data setup below:
data have;
input id time event;
datalines;
1 1 0
1 2 0
1 3 1
1 4 0
2 1 0
2 2 1
2 3 1
2 4 1
3 1 0
3 2 1
3 3 0
3 4 1
run;
To make @Astounding 's code a little neater, I'd have flag numeric:
data want;
set have;
by id;
if first.id then flag = 1;
retain flag;
if flag;
if event = 1 then flag = 0;
/* if you are sure about the contents of event, you can change this to */
flag = not event;
run;
but that's my personal preference.
To make @Astounding 's code a little neater, I'd have flag numeric:
data want;
set have;
by id;
if first.id then flag = 1;
retain flag;
if flag;
if event = 1 then flag = 0;
/* if you are sure about the contents of event, you can change this to */
flag = not event;
run;
but that's my personal preference.
FWIW
data have;
input id time event;
datalines;
1 1 0
1 2 0
1 3 1
1 4 0
2 1 0
2 2 1
2 3 1
2 4 1
3 1 0
3 2 1
3 3 0
3 4 1
4 1 0
4 2 0
4 3 0
;
run;
proc sql;
create table want as
select a.*
from have a left join (select id,time from have where event=1 group by id having time=min(time)) b
on a.id=b.id
where b.time=. or a.time<=b.time
order by a.id,a.time;
quit;
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.