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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: