• I have two datasets , and have to join them based on two criteria.
  1. if the primary key (ID) matches .
  2. If the date on the the second dataset is within -5days of the date in the dataset one.

Any idea how to do that ?

CodePudding user response：

You can use <= and >= operators on JOIN clause too. Your query may look something like this:

SELECT * 
FROM table_a as a
INNER JOIN table_b as b
  ON a.id=b.id 
  AND b.date >= a.date-5 
  AND b.date <= a.date 5

CodePudding user response：

Use BETWEEN operator.

  proc sql;
    create table want as
    select *
    from have1 t1 inner join have2 t2
        on t1.id = t2.id 
        and t2.date BETWEEN t1.date-5 AND t1.date 5
        ;
    quit;

With example data:

data have1;
infile datalines4 delimiter="|";
input id date :date9.;
format date date9.;
datalines4;
1|15DEC2021
2|12NOV2020
3|10JAN2019
;;;;

data have2;
infile datalines4 delimiter="|";
input id date :date9.;
format date date9.;
datalines4;
1|20DEC2021
2|16NOV2020
3|19JAN2019
;;;;

proc sql;
create table want as
select t1.id
from have1 t1 inner join have2 t2
    on t1.id = t2.id 
    and t2.date BETWEEN t1.date-5 AND t1.date 5
    ;
quit;

Result:
   id 
   1
   2