Hash join equivalent on PROC SQL between

Question

I usually use PROC SQL for when I'm joining a table on that also has a date condition (i.e. target_date falls between start_date and end_date).

I've been able to successfully translate this to a hash join when considering an INNER JOIN:

data hash_join;
if _n_ = 1 then do;
    declare hash add1(dataset:'table_2',multidata: 'Y');
    add1.defineKey('key_1');
    add1.defineData('start_date','end_date','value_1');
    add1.defineDone();
end;

format 
    start_date date9.
    end_date date9.
    value_1 10.5
;

set table_1 (keep=key_1 target_date);

if add1.find() = 0 then do until (add1.find_next());
    if start_date le target_date le end_date then output;
end;
run;

Which is the same thing as:

proc sql;
create table sql_join as select
b.start_date,
b.end_date,
b.value_1,
a.key_1,
a.target_date
from table_1 a
inner join table_2 b
on  a.key_1 = b.key_1 and
a.target_date between b.start_date and b.end_date
;quit;

I'm having trouble figuring out what the equivalent would be to a LEFT JOIN though. For instance, if something doesn't JOIN, I'd want to output, which I think is straightforward:

if add1.find() ne 0 then output;

And if it JOINs and the date is between, that seems straightforward as well:

if add1.find() = 0 then do until (add1.find_next());
    if start_date le target_date le end_date then output;
end;

But how do I get the rest of the records from table_1 that might join, but don't have the target_date between the start_date and end_date? For instance, let's say table_2 is a start_date and end_date of a sale, and that sale didn't start until February 1st for a key_1 = 'Clothes'. If my table_1 has 'Clothes' and sales on January 1st, it will JOIN on the key, but I want to output the blank value. Any ideas on how to do this?

Any help would be much appreciated!

Answer 1

You just need to keep track of whether you've found a match or not. Since you're not using the hash find to track the 'between' part of things, you can't use that, so you just have to do it yourself.

See this example. Here I modify SASHELP.CLASS to look like your input tables, then add a bit of logic to see if anything was found.

data table_1;
  set sashelp.class;
  rename age=target_date name=key_1;
  drop height weight;
run;

data table_2;
  set sashelp.class;
  do _i = 1 to mod(_n_,3);
    start_date = age-3+_i;
    end_date = age+1-_i;
    if start_date le end_date then output;
  end;
  rename name=key_1 height=value_1;
  keep height weight start_date age end_date name;
run;

data hash_join;
if _n_ = 1 then do;
    declare hash add1(dataset:'table_2',multidata: 'Y');
    add1.defineKey('key_1');
    add1.defineData('start_date','end_date','value_1');
    add1.defineDone();
end;

format 
    start_date date9.
    end_date date9.
    value_1 10.5
;

set table_1 (keep=key_1 target_date);

if add1.find() = 0 then do until (add1.find_next());
    if start_date le target_date le end_date then do;
      found=1;
      output;
    end;
end;
call missing(of value_1);  *full list of values to clear - all of hash data elements;
if not (found) then output;
run;

Answer 2

I think you just need to track if something has the key, but not in the range:

if add1.find() ^=0 then output;
else do;
   found = 0;
   do until (add1.find_next());
       if start_date le target_date le end_date then do;
          output;
          found=1;
       end;
   end;
   if ^found then output;
end;

No data to test with, so this is just me coding in SO. Let me know if it doesn't work.