topic Re: Left Join not Correct When using Where Clause in SAS Programming

Left Join not Correct When using Where Clause

Doug____ — Wed, 16 Oct 2019 16:56:03 GMT

I have a left table with 153 unduplicated observations and a right table with 15000+ observations.

If I use a where clause on the left table it only returns 153 records. If I remove the where clause and subset the data prior to query processing it returns the correct result (201 records). Why does this happen? Should it not return the same number of records? It acts like an inner join if the where clause is present regardless of the join used.

This returns 201 records (correct);
proc sql;
create table checkit as
select
a.an,
b.an as cfa,
b.load_date,
b.amount,
b.data_source,
b.fa_code
from
list a
left join
rfcorex b<<<-subset prior to query processing
on
a.an= b.an
;

This returns 153 records (inner join behavior - not correct)

proc sql;
create table checkit as
select
a.an,
b.an as cfa,
b.load_date,
b.amount,
b.data_source,
b.fa_code
from
list a
left join
rfcore b<<<-no subset prior to query processing
on
a.an= b.an

where b.data_source contains "XXXXX"

Re: Left Join not Correct When using Where Clause

PaigeMiller — Wed, 16 Oct 2019 16:59:54 GMT

@Doug____ wrote:

I have a left table with 153 unduplicated observations and a right table with 15000+ observations.

If I use a where clause on the left table it only returns 153 records. If I remove the where clause and subset the data prior to query processing it returns the correct result (201 records). Why does this happen? Should it not return the same number of records? It acts like an inner join if the where clause is present regardless of the join used.

These questions are impossible to answer without having the exact data sets (and code to do the subsetting) that you are using.

Perhaps you could make a smaller example that illustrates the problem, say 10 to 20 records, and provide that to us following these instructions (do not skip this step): https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Left Join not Correct When using Where Clause

Tom — Wed, 16 Oct 2019 17:03:18 GMT

WHERE filters on the way in.

If you want to filter after the join use HAVING.

Re: Left Join not Correct When using Where Clause

ballardw — Wed, 16 Oct 2019 17:03:39 GMT

The Where clause you have is applied after the join in the second case so ONLY includes any records where the clause is true.

Left join in the first case can create multiple records from match AND all of the records from the A data set are included regardless of the value of any of the B variables, such as not present in the B set at all based on the ON criteria.

data work.one;
   input x y;
datalines;
1 1
2 2
3 3
;

data work.two;
   input x y;
datalines;
5  3
;

proc sql;
   create table work.merged as
   select a.x , b.y
   from work.one as a
       left join
       work.two as b
       on a.x=b.x
   ;
quit;
/* result 3 records, all from work.one*/
proc sql;
   create table work.merged2 as
   select a.x , b.y
   from work.one as a
       left join
       work.two as b
       on a.x=b.x
   where b.y=3         
   ;
quit;
/* result 0 records because none of x match and the where would only
   output matches where the work.two y value is 3*/

Re: Left Join not Correct When using Where Clause

Doug____ — Mon, 28 Oct 2019 14:08:44 GMT

I took the where clause off and just did the join first then selected the records I wanted which seems to work.