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;
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;
I tried AND too instead of where and that bring me 320K records back.
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;
Thank you! It worked.
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.