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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.