/*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*/
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
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
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
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;
Hi Patrick,
Thank you very much.
Mirisa
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.