Help using Base SAS procedures

Merge to Exclude Certain Cases

Reply
Regular Contributor
Posts: 164

Merge to Exclude Certain Cases

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.

Frequent Contributor
Frequent Contributor
Posts: 94

Re: Merge to Exclude Certain Cases

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

Super Contributor
Posts: 578

Re: Merge to Exclude Certain Cases

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;

Contributor
Posts: 22

Re: Merge to Exclude Certain Cases

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

Ask a Question
Discussion stats
  • 3 replies
  • 165 views
  • 0 likes
  • 4 in conversation