topic Re: link dates from unbalanced data in SAS Programming

link dates from unbalanced data

Leo66 — Mon, 13 Nov 2017 22:03:38 GMT

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

Re: link dates from unbalanced data

PGStats — Mon, 13 Nov 2017 22:11:22 GMT

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;

Re: link dates from unbalanced data

Leo66 — Mon, 13 Nov 2017 22:26:50 GMT

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.

Re: link dates from unbalanced data

ballardw — Mon, 13 Nov 2017 22:41:45 GMT

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.

Re: link dates from unbalanced data

Leo66 — Mon, 20 Nov 2017 15:26:28 GMT

It works now, thanks for all your help!