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!
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.
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.
This worked perfectly. Thank you!
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.