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;
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')
Loose the distinct keyword.
It didn't help rather created the duplicate rows.
WHERE is applied the results of the LEFT JOIN.
Try moving those extra restrictions into the ON condition instead.
Thanks for your response Tom.
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')
BINGOOOOOOO!!!!! Awesome.
Thank you so much!
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.