Good day SAS users,
I have a question concerning data manipulation in a longitudinal analysis where I wanted to drop all observations after the 1st occurrence of an event. Suppose I had the following data set and I wanted to drop the observations that I have listed. They all occur after the first instance of an event. How would this be done?
Id | Time | Event | Drop |
1 | 1 | 0 |
|
1 | 2 | 0 |
|
1 | 3 | 1 |
|
1 | 4 | 0 | <- Drop |
2 | 1 | 0 |
|
2 | 2 | 1 |
|
2 | 3 | 1 | <- Drop |
2 | 4 | 1 | <- Drop |
3 | 1 | 0 |
|
3 | 2 | 1 |
|
3 | 3 | 0 | <- Drop |
3 | 4 | 1 | <- Drop |
Have a nice day,
-Carmine
This can easily be done by retaining a KEEPFLAG (1=keep >1 means don't keep).
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;
data want (drop=_:);
set have;
by id;
if first.id then _keepflag=1;
if _keepflag=1;
_keepflag+event;
run;
Notes:
1. Create a flag if the event has occurred.
2. Use RETAIN to keep the flag
3. If the flag is set delete the record.
4. Reset the flag at the first record for each ID
They end up being coded out of order because you want to not delete the record with the event.
data want;
set have;
by ID;
retain flag; *Step 2;
if first.ID then flag=0; *Step 4;
if flag=1 then delete; *Step 3;
if event=1 then flag=1; *Step 1;
run;
@Carmine_Rossi wrote:
Good day SAS users,
I have a question concerning data manipulation in a longitudinal analysis where I wanted to drop all observations after the 1st occurrence of an event. Suppose I had the following data set and I wanted to drop the observations that I have listed. They all occur after the first instance of an event. How would this be done?
Id
Time
Event
Drop
1
1
0
1
2
0
1
3
1
1
4
0
<- Drop
2
1
0
2
2
1
2
3
1
<- Drop
2
4
1
<- Drop
3
1
0
3
2
1
3
3
0
<- Drop
3
4
1
<- Drop
Have a nice day,
-Carmine
Good day Reeza,
Thank you for your help. The code provided seems to stop at the first event and then drops everything else, including all other patients after the first event has been identified. It is what I wanted to do, but by study_ID. I didn't forget the BY statement you provided in your code. Even with it, it does as described in my second sentence.
Thanks again,
Carmine
With your sample data and my exact code from the previous post it works exactly as you specified. See the tested version below.
Please post your code and log if you're having issues or explain in detail how it doesn't meet your guidelines. Its possible I overlooked something.
In the future please post your data as at minimum text or a preferably data step. I took the time to write code to generate your data this time but won't again.
data have;
do ID=1 to 3;
do time=1 to 4;
event=0;
if (id=1 and time=3) or (id=2 and time in(2, 3, 4)) or id=3 and time in (2,
4) then
event=1;
output;
end;
end;
run;
proc sort data=have;
by id time;
run;
data want;
set have;
by ID;
retain flag;
*Step 2;
if first.ID then
flag=0;
*Step 4;
if flag=1 then
delete;
*Step 3;
if event=1 then
flag=1;
*Step 1;
run;
proc print data=have;
proc print data=want;
run;
@Carmine_Rossi wrote:
Good day Reeza,
Thank you for your help. The code provided seems to stop at the first event and then drops everything else, including all other patients after the first event has been identified. It is what I wanted to do, but by study_ID. I didn't forget the BY statement you provided in your code. Even with it, it does as described in my second sentence.
Thanks again,
Carmine
This can easily be done by retaining a KEEPFLAG (1=keep >1 means don't keep).
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;
data want (drop=_:);
set have;
by id;
if first.id then _keepflag=1;
if _keepflag=1;
_keepflag+event;
run;
Notes:
Thank you very much mkeintz! This is what I was looking for. I appreciate the time you and others have contributed in helping to answer this question!
All the best,
-Carmine
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.