Hello
I am using SAS 9.3. I have a dataset where one record is one hospital visit.
I am running the data by health card number (unique) and I want to exclude ALL records per health card number when one of the visits indicates death.
I know about using merge and "not in" but that was for a single record; this is excluding any record based on health card number. I imagine I would still create a separate data set of deaths but just not sure how to remove all versus just the record not in.
Any and all assistance greatly appreciated.
It will be helpful to see an example of your dataset.
proc sql;
create table want
as select * from have t1
where not exists (select * from want where health_card_number=t1.health_card_number and death_indicator=true);
quit;
or
proc sql;
create table want as
select
t1.*
from
have t1
left outer join (select distinct health_card_number from want where death_indicator=true) t2
t1.health_card_number=t2.health_card_number
where
t2.health_card_number is null;
quit;
shellp55,
DBailey's approach should work. Below is the solution I offer.
If death_indicator = (0 : lived, 1 : died), then the following approaches should clean your dataset of deceased patients:
data patients;
input card_number visit death_indicator 8.;
datalines;
1 1 0
1 2 0
1 3 0
2 1 0
2 2 1
3 1 0
3 2 0
;
run;
APPROACH ONE
proc sql;
create table living_patients as
select a.*
from patients as a
left join (select distinct * from patients where death_indicator = 1) as b
on a.card_number = b.card_number
where b.death_indicator ne 1;
quit;
APPROACH TWO
proc sort data=patients;
by card_number descending death_indicator;
run;
data living_patients;
set patients;
by card_number descending death_indicator;
retain deceased_card_number;
if first.card_number and death_indicator = 1 then deceased_card_number = card_number;
if card_number ne deceased_card_number;
drop deceased_card_number;
run;
Hope this helps,
Huey
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!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.