topic Re: chain of SQL joinings in SAS Programming

chain of SQL joinings

dunga — Wed, 11 Jan 2017 15:50:44 GMT

/*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*/

Re: chain of SQL joinings

Steelers_In_DC — Wed, 11 Jan 2017 16:09:40 GMT

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

Re: chain of SQL joinings

dunga — Wed, 11 Jan 2017 21:30:18 GMT

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

Re: chain of SQL joinings

Patrick — Thu, 12 Jan 2017 15:39:00 GMT

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;

Re: chain of SQL joinings

dunga — Thu, 12 Jan 2017 14:24:11 GMT

Hi Patrick,

Thank you very much.

Mirisa