link dates from unbalanced data

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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
Esteemed Advisor
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

View solution in original post


All Replies
Solution
‎11-20-2017 10:25 AM
Esteemed Advisor
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 208 views
  • 5 likes
  • 3 in conversation