DATA Step, Macro, Functions and more

Proc Sql - join 3 tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Proc Sql - join 3 tables

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!

 


Accepted Solutions
Solution
‎02-24-2016 03:59 PM
Super User
Posts: 19,855

Re: Proc Sql - join 3 tables

Posted in reply to lai302120

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.

View solution in original post


All Replies
Solution
‎02-24-2016 03:59 PM
Super User
Posts: 19,855

Re: Proc Sql - join 3 tables

Posted in reply to lai302120

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.

Occasional Contributor
Posts: 12

Re: Proc Sql - join 3 tables

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
;

 

 

Occasional Contributor
Posts: 12

Re: Proc Sql - join 3 tables

Posted in reply to lai302120

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

 

thanks for the help!

Super User
Posts: 19,855

Re: Proc Sql - join 3 tables

Posted in reply to lai302120

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 274 views
  • 0 likes
  • 2 in conversation