SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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

[ Edited ]

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 Corrections2

108 Hospital

112 Hospital

112 Corrections3

112 Corrections3

117 Hospital

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!  

 


Accepted Solutions
Solution
‎06-06-2016 02:03 PM
Super User
Posts: 11,343

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

Posted in reply to mphqueens

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.

 

View solution in original post


All Replies
Solution
‎06-06-2016 02:03 PM
Super User
Posts: 11,343

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

Posted in reply to mphqueens

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.

 

Occasional Contributor
Posts: 15

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

This worked perfectly. Thank you!

Super User
Posts: 10,041

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

Posted in reply to mphqueens
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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 257 views
  • 0 likes
  • 3 in conversation