Identify patients across several lines based on lack of a specific value in any line

[jspoend]

Hi there, 

I am working with a dataset of midwive billing codes. The dataset has one line per midwive visit, so several lines per patient. 

The datafile is structured as follows: 

PatID VisitID location purpose

1 1 hospital postpartum

1 2 home postpartum

1 3 home postpartum

2 1 home  postpartum

2 2 home postpartum

3 1 hospital postpartum

4 1 home  postpartum

4 2 home postpartum

4 3 home postpartum

4 4 home postpartum

I need to identify and exclude those patients, who exclusively had visits at location 'hospital' and none at home. So some code checking if they had any visit at home and if not they would be flagged. If they had a first visit at hospital with subsequent visits at home they stay in. i.e. patient 3 would have to be excluded. 

I'm not sure how to best go about this, so any inputs are appreciated. 

Many thanks in advance, Julia 

[PeterClemmensen · Accepted solution]

How about

data have;
input PatID VisitID location $ purpose :$10.;
datalines;
1 1 hospital postpartum
1 2 home     postpartum
1 3 home     postpartum
2 1 home     postpartum
2 2 home     postpartum
3 1 hospital postpartum
4 1 home     postpartum
4 2 home     postpartum
4 3 home     postpartum
4 4 home     postpartum
;

data want;
   if _N_ = 1 then do;
      dcl hash h(dataset : "have(where = (location = 'home'))");
      h.definekey('PatID');
      h.definedone();
   end;
   
   set have;
   
   if h.check() = 0;
run;

[jspoend]

Dear Peter, 

Both of your answers seem to work perfectly, thanks a lot!

Julia

[PeterClemmensen]

Anytime 🙂

[PeterClemmensen]

Or

proc sql;
   create table want as
   select * from have
   group by PatID
   having sum(location = 'home')
   ;
quit;