BookmarkSubscribeRSS Feed
shellp55
Quartz | Level 8

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.

3 REPLIES 3
VD
Calcite | Level 5 VD
Calcite | Level 5

It will be helpful to see an example of your dataset.

DBailey
Lapis Lazuli | Level 10

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;

hdodson_pacificmetrics_com
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1054 views
  • 0 likes
  • 4 in conversation