Solved
New Contributor
Posts: 3

# link dates from unbalanced data

[ Edited ]

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

Accepted Solutions
Solution
‎11-20-2017 10:25 AM
Posts: 5,474

## Re: link dates from unbalanced data

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;``````
PG

All Replies
Solution
‎11-20-2017 10:25 AM
Posts: 5,474

## Re: link dates from unbalanced data

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;``````
PG
New Contributor
Posts: 3

## Re: link dates from unbalanced data

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.

Super User
Posts: 13,286

## Re: link dates from unbalanced data

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.

New Contributor
Posts: 3

## Re: link dates from unbalanced data

It works now, thanks for all your help!

☑ This topic is solved.