topic Re: Merge to Exclude Certain Cases in SAS Procedures

Merge to Exclude Certain Cases

shellp55 — Tue, 05 Feb 2013 15:15:02 GMT

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.

Re: Merge to Exclude Certain Cases

VD — Tue, 05 Feb 2013 16:48:37 GMT

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

Re: Merge to Exclude Certain Cases

DBailey — Tue, 05 Feb 2013 17:30:55 GMT

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;

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;

Re: Merge to Exclude Certain Cases

hdodson_pacificmetrics_com — Tue, 05 Feb 2013 19:21:52 GMT

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