topic Re: Question About joins in PROC sql in SAS Programming

Question About joins in PROC sql

mrdlau — Tue, 03 Apr 2018 22:21:47 GMT

Suppose i have 3 tables:
My query is essentially
Select *
From T1
Inner join T2 on T1
Inner join T3 on T2
Where T1.filter = somefilter.

I understand T2 will join into the filtered T1 because of the where statement. Does T3 join on the newly filtered T2(because of T1), or will it try to join T2 as if it is unfiltered

Re: Question About joins in PROC sql

PGStats — Tue, 03 Apr 2018 22:36:26 GMT

Your ON join-conditions are not syntactically correct. They should read ON T1.something1 = T2.something2.

Re: Question About joins in PROC sql

mrdlau — Tue, 03 Apr 2018 22:38:34 GMT

Thanks. I know my joints aren’t correct from a syntax perspective. I was just asking from a conceptual perspective. I.e. can I join 1 and 2 and then 3 and 2 in one join

Re: Question About joins in PROC sql

kiranv_ — Wed, 04 Apr 2018 01:36:37 GMT

best way to understand by making up data and try out various scenarios. This will tremendously increase your knowledge. check with on clause initially then try where clauses. Nothing better than learning by doing.

Re: Question About joins in PROC sql

ChrisNZ — Wed, 04 Apr 2018 23:09:16 GMT

@mrdlau > can I join 1 and 2 and then 3 and 2 in one join
Yes you can. And any other combination.

Why even ask? As @kiranv_ said, just try it.

You'll have the answer faster, and you'll learn better.

Re: Question About joins in PROC sql

Kurt_Bremser — Wed, 04 Apr 2018 06:17:05 GMT

And while you're doing your tests, as @kiranv_ suggested, also try a solution with sort and data steps, where you do the joins piecemeal.

With larger datasets, the overall performance might surprise you.

Caveat: This is only possible with 1-to-many, not with many-to-many relationships, where SQL is needed for the cartesian join.