BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eric_balke
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

5 REPLIES 5
ballardw
Super User

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.

eric_balke
Calcite | Level 5

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!

ballardw
Super User

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;

PGStats
Opal | Level 21

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
eric_balke
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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