topic Re: Deleting observations for an individual after an event in SAS Data Management

Deleting observations for an individual after an event

bgosiker — Sat, 06 Jul 2019 03:30:28 GMT


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;

Re: Deleting observations for an individual after an event

Astounding — Sat, 06 Jul 2019 05:09:13 GMT

One way:

data want;
set have;
by id;
if first.id then flag="Y";
retain flag;
if flag = "Y" then output;
if event = 1 then flag = "N";
run;

Re: Deleting observations for an individual after an event

Kurt_Bremser — Sat, 06 Jul 2019 05:38:19 GMT

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.

Re: Deleting observations for an individual after an event

novinosrin — Sat, 06 Jul 2019 13:39:00 GMT

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;