Hi all,
I am performing a data extracting using proc sql, and I have ran into a problem I can't figure out. I think it may be a recursive join but am having trouble finding the solution. I am joining 12 tables with left outer joins, but the problem I am having is I need pcp info, rendering md info, and referring md info from a doctors table but they are all joined to my initital table with the same primary key, doctor number.
Visually here is a boiled down version of what I have in the billing data:
patient_id pcp ref_md rend_md
1 20 21 22
3 30 31
Here is what i have for the doctors table:
doctor_id doctor_name doctor_phone etc....
20 Tom 33333
21 Jane 44444
22 Mary 55555
30 Andy 66666
31 Bill 77777
What I want to have as the output is:
patient_id pcp pcp_name pcp_phone ref_md ref_name ref_phone rend_md rend_name rend_phone
1 20 Tom 33333 21 Jane 44444 22 Mary 55555
I have to include this along with the 11 other left outer joins in my PROC SQL step also. Any help is greatly appreciated!
You simply need to join with the doctor table twice, as in :
data patient;
input patient_id pcp ref_md rend_md;
datalines;
1 20 21 22
3 30 31 .
;
data doctor;
input doctor_id doctor_name $ doctor_phone;
datalines;
20 Tom 33333
21 Jane 44444
22 Mary 55555
30 Andy 66666
31 Bill 77777
;
proc sql;
create table patDoc as
select
p.patient_id, p.pcp,
p.ref_md,
d1.doctor_name as ref_name,
d1.doctor_phone as ref_phone,
p.rend_md,
d2.doctor_name as rend_name,
d2.doctor_phone as rend_phone
from
patient as p left join
doctor as d1 on ref_md=d1.doctor_id left join
doctor as d2 on rend_md=d2.doctor_id;
select * from patDoc;
quit;
PG
It looks like you want 3 joins one on PCP = doctor_id, one on ref_md=Doctor_id and one on rend_md=doctor_id.
OR if your list of doctors isn't too volatile you could create a custom format that links the doctor_id to name and phone etc.
proc format;
value doc_id_2_name;
20 = 'Tom'
21 = 'Jane'
etc;
value doc_id_2_phone
(Actually use the doctor table to create a CNTLIN data set for Proc format).
The you could create the values in you sql as
put(pcp,doc_id_2_name.) as Pcp_name, put(pcp,doc_id_2_phone.) as pcp_phone,
put(Ref_md,doc_id_2_name.) as Ref_name, put(Ref_med,doc_id_2_phone.) as Ref_phone,
and so forth.
That's a great idea to use Proc Format to create a format to apply in the Proc SQL select statement. I will try it out and post back if/when I can get it to work. Thank you!
One caveat, depending on how your doctor information table is created: the doctor_id better be unique (which would have been a problem for joins anyway) but proc format will fail if one doctor_id is associated with 2 names for instance.
Here is an example of taking a list data set similar to to your doctor info and creating multiple formats for the same value. Note the SORT before the Proc Format is important.
data service.fpsitefmt (keep=fmtname start label );
set service.fpsites;
fmtname="$FPSiteCity";
start=site;
label=sitecity;
output;
fmtname="$FPSiteHD";
start=site;
label=district;
output;
fmtname="$FPSiteZip";
start=site;
label=SiteZip;
output;
run;
proc sort data=service.fpsitefmt; by fmtname label;run;
proc format cntlin=service.fpsitefmt library=work;
run;
You simply need to join with the doctor table twice, as in :
data patient;
input patient_id pcp ref_md rend_md;
datalines;
1 20 21 22
3 30 31 .
;
data doctor;
input doctor_id doctor_name $ doctor_phone;
datalines;
20 Tom 33333
21 Jane 44444
22 Mary 55555
30 Andy 66666
31 Bill 77777
;
proc sql;
create table patDoc as
select
p.patient_id, p.pcp,
p.ref_md,
d1.doctor_name as ref_name,
d1.doctor_phone as ref_phone,
p.rend_md,
d2.doctor_name as rend_name,
d2.doctor_phone as rend_phone
from
patient as p left join
doctor as d1 on ref_md=d1.doctor_id left join
doctor as d2 on rend_md=d2.doctor_id;
select * from patDoc;
quit;
PG
That worked! I thought it might be a simple solution...thank you!
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.