I have a dataset with patient_type=living and patient_type=deceased, as well as a column for match_ID.
I want to keep only observations where patient_type=living and patient_type=deceased have the same match_ID, and exclude everything else.
For example,
Key Px_Type Match_ID
1115 L 0001a
1116 D 0001a
1117 D 0004b
1118 L 0003b
I want to keep observations 1115 (L) and 1116 (D) since they have the same match_ID. Is there a way to approach this in SAS?
data have;
input Key Px_Type $ Match_ID $;
cards;
1115 L 0001a
1116 D 0001a
1117 D 0004b
1118 L 0003b
;
run;
proc sql;
select *
from have
group by Match_ID
having count(distinct Px_Type) ne 1;
quit;
If your data is sorted then you could try a statement like this
if compress(Match_ID) eq lag(compress(Match_ID)) and compress(Px_Type) in ( "D","L") then output ;
Is there other data that has to be preserved? If so, what exactly would the result look like?
data have;
input Key Px_Type $ Match_ID $;
cards;
1115 L 0001a
1116 D 0001a
1117 D 0004b
1118 L 0003b
;
run;
proc sql;
select *
from have
group by Match_ID
having count(distinct Px_Type) ne 1;
quit;
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.