link dates from unbalanced data

[Leo66]

Hi I have two data sets

data one:

id  date1

1  01/01/2016

1  01/08/2016

1  02/06/2016

1  03/07/2016

2  01/05/2016

2  01/12/2016

2  02/09/2016

2  03/11/2016

3  01/02/2016

3  01/03/2016

3  02/08/2016

3  03/03/2016

data two:

id date2

1 01/03/2016

1 01/09/2016

1 03/08/2016

2 01/06/2016

2 03/12/2016

3 01/04/2016

3 02/09/2016

3 03/04/2016

how to merge these two data sets by the condition that 0<=(date2-date1)<=2 days by ID?

The final data should be like:

id date1 date2

1  01/01/2016   01/03/2016

1  01/08/2016   01/09/2016

1  02/06/2016

1  03/07/2016   03/08/2016

2  01/05/2016   01/06/2016

2  01/12/2016

2  02/09/2016

2  03/11/2016   03/12/2016

3  01/02/2016   01/04/2016

3  01/03/2016   01/04/2016

3  02/08/2016   02/09/2016

3  03/03/2016   03/04/2016

Please note the two data has different # of id.

Thanks,

Leo

[PGStats · Accepted solution]

Relatively simple with SQL:

proc sql;
create table three as
select 
    a.id,
    a.date1,
    b.date2
from 
    one as a left join
    two as b on a.id=b.id and intck("day", date1, date2) between 0 and 2
order by id, date1, date2;
quit;

[Leo66]

Thanks! I tried your code but got the following error message:

ERROR: Function INTCK requires a numeric expression as argument 2.
ERROR: Function INTCK requires a numeric expression as argument 3.
ERROR: Expression using IN has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during
PROC SQL WHERE clause optimization.
ERROR: The following columns were not found in the contributing tables: date1, date2.

[ballardw]

INTCK requires numeric date values. Your dates are likely character values, which are atrocious for trying to calculate "within 2 days".

Convert them to SAS date values using input(datevar,mmddyy10.) in the INTCK calculations.

Better would be to go back in your process and insure that your variables are SAS date valued numeric for many reasons.

[Leo66]

It works now, thanks for all your help!