BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser_sk
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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')

View solution in original post

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

Loose the distinct keyword.

sasuser_sk
Quartz | Level 8

It didn't help rather created the duplicate rows.

Tom
Super User Tom
Super User

WHERE is applied the results of the LEFT JOIN.

Try moving those extra restrictions into the ON condition instead.

sasuser_sk
Quartz | Level 8

Thanks for your response Tom.

novinosrin
Tourmaline | Level 20

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')
sasuser_sk
Quartz | Level 8

BINGOOOOOOO!!!!! Awesome.

 

Thank you so much!

Reeza
Super User
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.
sasuser_sk
Quartz | Level 8

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!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1876 views
  • 1 like
  • 5 in conversation