topic Re: Left Join Problem in SAS Programming

Left Join Problem

sasuser_sk — Wed, 27 Jan 2021 18:15:18 GMT

Hello - I have 800 rows in my left table (no duplicates). When I Left join using code below it only extracts 250 rows and those are the ones that matched to left table. Can I please pick your brains to find error in my code? Thanks a lot!

PROC SQL;
Create table WORK.ABC AS
Select Distinct a.*, b.Name, b.Trans_date, b.Client_Add Date, b.Client_Type

From WORK.CUSTOMERS a
LEFT JOIN SERVRE.TRANSACTIONS b ON ((a.ACCT_NO = b.ACCT_NO) and (a.ID_NO = b.ID_NO))
WHERE (b.Name LIKE 'JE%')
AND ((DATEPART(b.Trans_Date) >= '13JUL2020'D)
AND (DATEPART(b.Trans_Date) <= '18JAN2021'D))

AND (b.Client_Type = 'Continuously')

order by a.ACCT_NO,
a.ID_NO;
QUIT;

Re: Left Join Problem

PeterClemmensen — Wed, 27 Jan 2021 18:17:07 GMT

Loose the distinct keyword.

Re: Left Join Problem

Tom — Wed, 27 Jan 2021 18:17:36 GMT

WHERE is applied the results of the LEFT JOIN.

Try moving those extra restrictions into the ON condition instead.

Re: Left Join Problem

novinosrin — Wed, 27 Jan 2021 18:19:25 GMT

HI @sasuser_sk Can you try replacing WHERE with AND

LEFT JOIN SERVRE.TRANSACTIONS b ON ((a.ACCT_NO = b.ACCT_NO) and (a.ID_NO = b.ID_NO))
AND (b.Name LIKE 'JE%') 
          AND ((DATEPART(b.Trans_Date) >= '13JUL2020'D)
         AND (DATEPART(b.Trans_Date) <= '18JAN2021'D))

        AND (b.Client_Type = 'Continuously')

Re: Left Join Problem

sasuser_sk — Wed, 27 Jan 2021 18:25:09 GMT

It didn't help rather created the duplicate rows.

Re: Left Join Problem

sasuser_sk — Wed, 27 Jan 2021 18:27:06 GMT

BINGOOOOOOO!!!!! Awesome.

Thank you so much!

Re: Left Join Problem

sasuser_sk — Wed, 27 Jan 2021 18:27:53 GMT

Thanks for your response Tom.

Re: Left Join Problem

Reeza — Wed, 27 Jan 2021 18:30:10 GMT

What makes you think you have an error? If you're getting an error in the log, please post it. If it's because you're expecting 800 records, that's not quite right.
The WHERE clause is filtering out data so you're losing those records. Note that character comparisons are case sensitive so if you have Je it will not be included in your output.

To determine how your records are being filtered you can play around with the WHERE clause to see which one is limiting the records.

Re: Left Join Problem

sasuser_sk — Wed, 27 Jan 2021 19:11:36 GMT

Playing around with WHERE worked fine and I got all the records from left table back along with the data that matched with them. Thx!