topic Re: PROC SQL in SAS Programming

PROC SQL

eric_balke — Fri, 06 Mar 2015 22:47:43 GMT

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!

Re: PROC SQL

ballardw — Fri, 06 Mar 2015 23:11:54 GMT

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.

Re: PROC SQL

eric_balke — Fri, 06 Mar 2015 23:16:28 GMT

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!

Re: PROC SQL

PGStats — Fri, 06 Mar 2015 23:19:57 GMT

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;

Re: PROC SQL

ballardw — Fri, 06 Mar 2015 23:27:54 GMT

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;

Re: PROC SQL

eric_balke — Fri, 06 Mar 2015 23:42:25 GMT

That worked! I thought it might be a simple solution...thank you!