topic Re: Proc Sql - join 3 tables in SAS Programming

Proc Sql - join 3 tables

lai302120 — Wed, 24 Feb 2016 19:42:39 GMT

Hi all,

I'm trying to join 3 tables in on proc sql statement and i can't seem to figure out how to do so...

the code below is what i have:

proc sql;

create table test as

select a.store, b.name, c.type

from data1 a

inner join data2 b

on a.id = b.id

left join data3 c

on b.id2=c.id2

and

<exclusion statements>...

;

quit;

is there something wrong with my code?

thanks for your help!

Re: Proc Sql - join 3 tables

Reeza — Wed, 24 Feb 2016 19:47:39 GMT

Nothing wrong with your code, which means there's something wrong in the logic.

Things to check - join types inner vs left vs right

And your exclusion criteria. You can run it without and then with to see if that helps.

Re: Proc Sql - join 3 tables

lai302120 — Wed, 24 Feb 2016 20:54:58 GMT

Thanks, Reeza.

I ran the code with the exclusion statements and the dataset created seemed to have ignored all the exclusion criteria i wrote.

do you have any ideas as to why this might happen?

exclusion statements:

....

and b.sls_typ ^= '05'
and ( (b.xtnd_prc > 0.01) or (b.xtnd_prc < -0.01) )
and b.trans_dt >= '03JAN2016'd/*&beg_dt1*/
and b.trans_dt <= '04JAN2016'd/*&end_dt1*/
and a.ADDR_ID ^= .
order by a.ADDR_ID
;

Re: Proc Sql - join 3 tables

lai302120 — Wed, 24 Feb 2016 20:59:49 GMT

ahh nevermind. the code is working out...my datasets timed out so they weren't being read in.

thanks for the help!

Re: Proc Sql - join 3 tables

Reeza — Wed, 24 Feb 2016 21:00:06 GMT

They belong in a WHERE most likely, not on the join condition.