topic Re: Multiple lines per patient extracted based on the presence of one observation among them in SAS Data Management

Multiple lines per patient extracted based on the presence of one observation among them

mphqueens — Mon, 06 Jun 2016 17:20:48 GMT

I am looking to make a subset of patients that at one point were tested at a correctional facility. Each patient number has multiple associated datalines (rows) but I wanted to extract all of them, even if they were only seen in my specified facilities once. For example:

data have;

input Patient_ID $ Submitter_Name $;

datalines;

104 FamilyDoc

104 Corrections1

104 Hospital

106 FamilyDoc

106 Hospital

108 Corrections2

108 Hospital

112 Hospital

112 Corrections3

117 Hospital

;

run;

What I would like to do is make a subset of only those patient IDs who were at one point tested in the facilities labelled "Corrections1, Corrections2, Corrections3". However, I need all lines from the associated patient ID. In this case, only the lines for patients 104, 108 and 112 should be output.

Can anyone help me with the code for this?

Thanks!

Re: Multiple lines per patient extracted based on the presence of one observation among them

ballardw — Mon, 06 Jun 2016 17:48:28 GMT

Try

data have; 
informat Patient_ID $3. Submitter_Name $15.;
input  Patient_ID $ Submitter_Name $; 

datalines; 
104 FamilyDoc
104 Corrections1
104 Hospital
106 FamilyDoc
106 Hospital 
108 Corrections2
108 Corrections2
108 Hospital
112 Hospital
112 Corrections3
112 Corrections3
117 Hospital
117 Hospital
;
run;

proc sql;
   create table want as
   select b.*
   from (select distinct Patient_ID from have where Submitter_Name in ( "Corrections1", "Corrections2", "Corrections3")) as a
        left join
        have as b on 
        a.Patient_ID = b.Patient_ID;
quit;

You didn't provide a length to the Submitter_name variable in the example that would hold the full text of the requested names.

Re: Multiple lines per patient extracted based on the presence of one observation among them

mphqueens — Mon, 06 Jun 2016 18:03:53 GMT

This worked perfectly. Thank you!

Re: Multiple lines per patient extracted based on the presence of one observation among them

Ksharp — Tue, 07 Jun 2016 02:28:50 GMT

data have; 
informat Patient_ID $3. Submitter_Name $15.;
input  Patient_ID $ Submitter_Name $; 
datalines; 
104 FamilyDoc
104 Corrections1
104 Hospital
106 FamilyDoc
106 Hospital 
108 Corrections2
108 Corrections2
108 Hospital
112 Hospital
112 Corrections3
112 Corrections3
117 Hospital
117 Hospital
;
run;

proc sql;
   create table want as
   select *
   from have
    group by Patient_ID
     having sum(Submitter_Name in ("Corrections1", "Corrections2", "Corrections3")) gt 0;
quit;