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!

Hash join equivalent on PROC SQL between

2 Answers