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

/*Hi SAS Forum,*/

 

/*My co-worker has done a chain of sql left joins like below. Could you

help me to understand it*/

 

data one;

input id limit;

cards;

111 15000

222 25000

;

run;

 

 

data two;

input id balance;

cards;

111 500

222 300

;

run;

 

 

data three_dwo;

input id dwo_status $ 5-7;

cards;

111 dwo

222 no

;

run;

 

 

 

/*Below is the clumsy SQL joining*/

 

proc sql;

/*I can understand below piece in which we associate "balance" field to our "one" table*/

create table combined as

select a.*

,b.balance

from one as a left join

     two as b

on a.id=b.id

 

/*Below is the step I cannot understand. To which table we do the left joining of "three_dwo" table?*/

left join three_dwo as c

on a.id=c.id;

quit;

 

 /*Thanks, Mirisa*/

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

Because the code doesn't select anything from c.table the last part isn't changing anything, but you could do this:

 

proc sql;
create table combined as
select a.*,b.balance,c.dwo_status
from one a left join
     two b
on a.id=b.id
left join three_dwo c
on a.id=c.id;
quit;

 

 

The second join links table C to the table that was created between A and B.  Because they are both left joins table B and C both left join to table A. 

Which would pull in the field from three_dwo

View solution in original post

4 REPLIES 4
Steelers_In_DC
Barite | Level 11

Because the code doesn't select anything from c.table the last part isn't changing anything, but you could do this:

 

proc sql;
create table combined as
select a.*,b.balance,c.dwo_status
from one a left join
     two b
on a.id=b.id
left join three_dwo c
on a.id=c.id;
quit;

 

 

The second join links table C to the table that was created between A and B.  Because they are both left joins table B and C both left join to table A. 

Which would pull in the field from three_dwo

dunga
Obsidian | Level 7

Hi Steelers,

 

Thank you very much.

 

I still have a few questions which are embedded in red color in your response below.

 

proc sql;
create table combined as
select a.*,b.balance,c.dwo_status
from one a left join
     two b
on a.id=b.id
left join three_dwo c  /*this is the second join*/
on a.id=c.id;
quit;

 

 

The second join links table C to the table that was created between A and B (the table created between A and B is "combined". So, this means second join links table C to table "combined". Am I right? If this is right, then the resultant table due to the second join is what? Is the resultant table is again "combined".? ).  Because they are both left joins table B and C both left join to table A. 

Which would pull in the field from three_dwo

Patrick
Opal | Level 21

It's a bit hard to read but if you're really interested how SAS processes the query then options _method and _tree write quite a bit of information to the log. 

Running below code will illustrate what you can get in the SAS Log. If you really want to understand how to interprete the log messages then Google for some of the very informative white papers which give you a deep dive into this.

data one;
  ona=1;
  do id= 1 to 5;
    output;
  end;
run;

data two;
  balance=10;
  do id= 1 to 4, 6, 7;
    output;
  end;
run;

data three_dwo;
  dwo_status=100;
  do id= 1 to 3, 7,8;
    output;
  end;
run;

  
proc sql _method _tree feedback;
  create table combined as
    select a.*,b.balance,c.dwo_status
      from one a left join
        two b
        on a.id=b.id
      left join three_dwo c  /*this is the second join*/
  on a.id=c.id;
quit;

 

dunga
Obsidian | Level 7

Hi Patrick,

Thank you very much.

 

Mirisa

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
  • 4 replies
  • 420 views
  • 1 like
  • 3 in conversation