Hi
I need help removing observations depending on previous observations.
I have
ID HA
1 1
1 3
2 3
2 3
2 3
3 2
3 3
3 3
4 1
4 2
4 3
I want
ID HA
1 1
2 3
2 3
2 3
3 2
4 1
For each ID I want to keep the observation where HA=1 or HA=2. If one ID has both HA=1 and HA=2, I only want to keep the one where HA=1. If an ID has neither HA=1 nor 2 (ie. only HA=3), then I want to keep all the observations with HA=3.
Any suggestions?
You could do it by sorting, then using first.id to pick off the first record. If it's a 1 or 2, you output it. If it's a 3, that means all records for that ID are 3's, and you want to output them all.
So you could do it like:
data have ;
input ID HA ;
cards ;
1 1
1 3
2 3
2 3
2 3
3 2
3 3
3 3
4 1
4 2
4 3
;
run ;
proc sort data=have ;
by id ha ;
run ;
data want ;
set have ;
by id ;
retain allthrees ;
if first.id then do ;
if HA IN (1,2) then output ;
allthrees=(HA=3) ;
end ;
if allthrees then output ;
run ;
@RTN wrote:
For each ID I want to keep the observation where HA=1 or HA=2. If one ID has both HA=1 and HA=2, I only want to keep the one where HA=1. If an ID has neither HA=1 nor 2 (ie. only HA=3), then I want to keep all the observations with HA=3.
What about the third case, where one ID has either HA=1 or HA=2, but not both?
@RTN wrote:
When one ID has neither HA=1 nor HA=2, then I want to keep all observations.
Actually, that wasn't my question.
You could do it by sorting, then using first.id to pick off the first record. If it's a 1 or 2, you output it. If it's a 3, that means all records for that ID are 3's, and you want to output them all.
So you could do it like:
data have ;
input ID HA ;
cards ;
1 1
1 3
2 3
2 3
2 3
3 2
3 3
3 3
4 1
4 2
4 3
;
run ;
proc sort data=have ;
by id ha ;
run ;
data want ;
set have ;
by id ;
retain allthrees ;
if first.id then do ;
if HA IN (1,2) then output ;
allthrees=(HA=3) ;
end ;
if allthrees then output ;
run ;
If understood your question correctly .
data have ;
input ID HA ;
cards ;
1 1
1 3
2 3
2 3
2 3
3 2
3 3
3 3
4 1
4 2
4 3
;
run ;
data want;
set have;
by id ha;
if first.id then n=0;
n+first.ha;
if n=1;
drop n;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.