SQL Left Join with WHERE clause SAS Enterprise Guide-CodePudding

I am trying to pair 2 tables (nicknamed PERSIP and ECIF) on their ID field, (labeled TABLE1 & TABLE2) to create a RESULTTABLE, where the ym_id (for both tables) variable is set to my timekey0 variable for a specific datetime.

I am wondering why this code produces 0 rows of resulting data. After looking online, this was the format people posted as solutions to similar problems.

%let timekey0 = 202110;
proc sql;

CREATE TABLE RESULTTABLE AS
SELECT

PERSIP.col1,
PERSIP.col2,
PERSIP.col3,

ECIF.col1,
ECIF.col2,
ECIF.col3,
ECIF.col4

FROM DB.TABLE1 PERSIP

LEFT JOIN DB.TABLE2 ECIF

ON PERSIP.ID = ECIF.ID 

WHERE ECIF.ym_id = &timekey0.

AND PERSIP.ym_id = &timekey0.;

quit;

I got a result of 0 rows with many columns. Not sure if my join type is incorrect but I have 0 rows in the table.

CodePudding user response：

There may be two reasons for this:

There is no records matching to your where criteria (ECIF.ym_id = &timekey0. AND PERSIP.ym_id = &timekey0.)
There is no records to join matching your on criteria (ON PERSIP.ID = ECIF.ID)

CodePudding user response：

Your logic seems off. You say you want a LEFT JOIN then use a variable from the "RIGHT" table in your WHERE condition.

Most likely you just want to add those conditions to the ON condition.

FROM TABLE1 PERSIP
LEFT JOIN TABLE2 ECIF
  ON PERSIP.ID = ECIF.ID 
  AND ECIF.ym_id = &timekey0.
  AND PERSIP.ym_id = &timekey0.

Or perhaps just keep the condition that will limit the observations read from the "LEFT" table in the WHERE condition

FROM TABLE1 PERSIP
LEFT JOIN TABLE2 ECIF
  ON PERSIP.ID = ECIF.ID 
  AND PERSIP.ym_id = ECIF.ym_id 
WHERE PERSIP.ym_id = &timekey0.