DATA Step, Macro, Functions and more

PROC SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

PROC SQL

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!


Accepted Solutions
Solution
‎03-06-2015 06:19 PM
Respected Advisor
Posts: 4,937

Re: PROC SQL

Posted in reply to eric_balke

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

PG

View solution in original post


All Replies
Super User
Posts: 11,343

Re: PROC SQL

Posted in reply to eric_balke

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.

Occasional Contributor
Posts: 6

Re: PROC SQL

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!

Super User
Posts: 11,343

Re: PROC SQL

Posted in reply to eric_balke

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;

Solution
‎03-06-2015 06:19 PM
Respected Advisor
Posts: 4,937

Re: PROC SQL

Posted in reply to eric_balke

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

PG
Occasional Contributor
Posts: 6

Re: PROC SQL

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 242 views
  • 6 likes
  • 3 in conversation