Proc SQL joining two tables and not receiving NULLs

Question

I have a SAS table that I'm joining with a single column from a SQL server table. The goal is to add the column from SQL Server when it matches or return NULL when it doesn't. However there are no NULLs in the final table.

Here's the code:

create table Final as
Select a.*,
     b.group
From a
Left Join b on a.id=b.id
where datepart(a.date) between b.start and b.end
     and b.void=0
     and b.current=1
     and b.deleted=0;

Even when a.id is not equal to b.id I want the row to populate and b.group to be null. There's 10,000 rows missing in my final table compared to my starting table and no null values in b.group.

Answer 1

Since by definition the observations with missing values of B.GROUP will also have missing values of B.VOID and the other variables in your WHERE clause they are eliminated.

Change the WHERE to an AND so the extra conditions are part of the join criteria instead of a filtering criteria.

create table Final as
select a.*
     , b.group
from a left join b
  on a.id=b.id
  and datepart(a.date) between b.start and b.end
  and b.void=0
  and b.current=1
  and b.deleted=0
;