topic Re: Left Join missing rows from left table in SAS Programming

Left Join missing rows from left table

sasuser_sk — Wed, 31 Mar 2021 19:44:57 GMT

Hi: I have 280K records in left table and over a million in table 2. My join brings me 100K records only. I don't understand the problem as to why data is dropped in left join. I have tried it two ways but both bring back 100K records. Help will be greatly appreciated.

Methods1:
PROC SQL;
CREATE TABLE WORK.ASSIGNS AS
SELECT DISTINCT t1.ACCT_NO,
t1.ACCT_NO_DET,
t1.PROD_ONE,
t1.PROD_TWO,
t1.START_DATE,
t1.END_DATE,
t2.PROD_DETAILS

FROM WORK.CUSTOMER t1
LEFT JOIN SCHEMA.PRODUCTS t2 ON (t1.ACCT = t2.ACCT_NO) AND (t1.ACCT_NO_DET = t2.ACCT_NO_DET)
WHERE t2.PROD_DETAILS LIKE 'ACCESSORY' ;
QUIT;

Method 2:

PROC SQL;
CREATE TABLE WORK.ASSIGNS AS
SELECT DISTINCT t1.ACCT_NO,
t1.ACCT_NO_DET,
t1.PROD_ONE,
t1.PROD_TWO,
t1.START_DATE,
t1.END_DATE,
t2.PROD_DETAILS

FROM WORK.CUSTOMER t1
LEFT JOIN SCHEMA.PRODUCTS t2 ON (t1.ACCT = t2.ACCT_NO) AND (t1.ACCT_NO_DET = t2.ACCT_NO_DET)
WHERE t2.PROD_DETAILS LIKE 'ACCESSORY' or t2.PROD_DETAILS is missing ;
QUIT;

Re: Left Join missing rows from left table

Reeza — Wed, 31 Mar 2021 19:46:50 GMT

Your WHERE clause is filtering out the records most likely.

Re: Left Join missing rows from left table

sasuser_sk — Wed, 31 Mar 2021 19:51:31 GMT

I tried AND too instead of where and that bring me 320K records back.

Re: Left Join missing rows from left table

PGStats — Wed, 31 Mar 2021 20:05:58 GMT

You must force the where clause to be evaluated before the join. Try this:

PROC SQL;
CREATE TABLE WORK.ASSIGNS AS
SELECT DISTINCT t1.ACCT_NO,
	t1.ACCT_NO_DET,
	t1.PROD_ONE,
	t1.PROD_TWO,
	t1.START_DATE,
	t1.END_DATE,
	t2.PROD_DETAILS
FROM 
	WORK.CUSTOMER as t1 LEFT JOIN 
	(	select * 
		from  SCHEMA.PRODUCTS
		WHERE PROD_DETAILS LIKE 'ACCESSORY'  ) as t2 
		ON t1.ACCT = t2.ACCT_NO AND t1.ACCT_NO_DET = t2.ACCT_NO_DET;
QUIT;

Re: Left Join missing rows from left table

sasuser_sk — Wed, 31 Mar 2021 20:14:23 GMT

Thank you! It worked.