Merge by date and date range

[MCB2]

I have two datasets as follows:

data one;
input ID Datex ;
informat datex yymmdd10.;
format Datex yymmdd10.;
datalines;
1 2021-10-10
1 2022-03-22
1 2022-08-15
2 2020-05-06
;
run;

data two;
input ID Dateint1 Dateint2;
informat Dateint1 Dateint2 yymmdd10.;
format Dateint1 Dateint2 yymndd10.;
datalines;
1 2021-10-03 2021-10-17
1 2022-04-01 2022-04-14
1 2022-08-08 2022-08-22
1 2022-12-02 2022-12-17

;
run;

My aim is to create 3 datasets by merging one with  two in different ways:

The first dataset I create is when the date variable in one fits within the date interval in dataset two. The code is:

data match;
merge one (in=a) two (in=b)
by Id;
if a and b and Dateint1<=Datex<=Dateint2 then output;
run;

The results  I get are as required:

ID    Datex                   Dateint1                      Dateint2
1     2021-10-10            2021-10-03               2021-10-17
1     2022-08-15            2022-08-08               2022-08-22

I need help with code for the next two tables:

A merge where data exist in table one but not in two. The results should look like this:

ID Datex             Dateint1           Dateint2
1 2022-03-22      .                         .
2 2022-05-06      .                         .

A merge where data do not exist in table one but exist in table two. The results should look like this:

ID   Datex       Dateint1         Dateint2
1     .              2022-04-01    2022-04-14
2     .              2022-12-02    2022-12-17

Thanks!

[Tom · Accepted solution]

The data step is not going to do this type of many-to-many merge properly (without a lot of extra programing).

Better to use SQL.

Looks like you just want to categorize the combinations.

proc sql ;
create table want as 
  select case when (not missing(one.id) and not missing(two.id)) then 1
              when (not missing(one.id)) then 2
              else 3 end as group
       , coalesce(one.id,two.id) as id
       , one.datex
       , two.dateint1
       , two.dateint2
  from one full join two
  on one.id=two.id and one.datex between two.dateint1 and two.dateint2
  order by group,id,datex,dateint1
;
quit;

Obs    group    id         datex      dateint1      dateint2

 1       1       1    2021-10-10    2021-10-03    2021-10-17
 2       1       1    2022-08-15    2022-08-08    2022-08-22
 3       2       1    2022-03-22             .             .
 4       2       2    2020-05-06             .             .
 5       3       1             .    2022-04-01    2022-04-14
 6       3       1             .    2022-12-02    2022-12-17