topic Join on offsetting dates in SAS Procedures

Join on offsetting dates

Sotark — Mon, 09 Mar 2015 20:07:34 GMT

Good Day All,

I am currently trying to join two tables that have date columns but I need the date in table b to be one day after the date in table 1.

Table A has account number, case number and a date an action occured. Table B has account number and a date a different action occured. I need the results from table A where the action in table B happened the next day. Table B can have multiple entries for each account number, but I only want the results where the action happened the next day. I have examples below. I have also tried a code but it does not work.

Table A

CM15	case_nbr	Action_dt
1234	A1234	1/1/2015
1235	A1235	1/12/2015
1236	A1236	1/20/2015
1237	A1237	1/20/2015
1238	A1238	1/23/2015

Table B

CM15	action_dt
1234	1/1/2002
1234	1/2/2015
1234	1/30/2015
1235	1/13/2015
1235	2/1/2015
1236	1/20/2015
1237	1/21/2015
1238	2/6/2015

Expected Results

CM15	case_nbr	action_dt	2nd_action_dt
1234	A1234	1/1/2015	1/2/2015
1235	A1235	1/12/2015	1/13/2015
1237	A1237	1/20/2015	1/21/2015

Re: Join on offsetting dates

slchen — Mon, 09 Mar 2015 20:48:45 GMT

proc sql;

select a.*,b.action_dt as sec_action_dt from tableA a,tableB b

where a.cm15=b.cm15 and a.action_dt+1=b.action_dt;

quit;

Re: Join on offsetting dates

DBailey — Mon, 09 Mar 2015 20:49:24 GMT

proc sql

create table ltr_check as

select a.cm15, a.case_nbr, a.action_dt, b.action_Dt as ltr_dt

from table a

left join table b

on a.cm15 = b.cm15 and intnx('day',a.action_dt,1)=b.action_dt

;

quit;

Re: Join on offsetting dates

Sotark — Mon, 09 Mar 2015 20:53:44 GMT

Thank you DBailey. It appears I had the right thought process, just the wrong syntax? Using the intnx did exactly what I needed.

Re: Join on offsetting dates

Haikuo — Mon, 09 Mar 2015 23:30:29 GMT

Are you implying that the INTNX works while '+1' doesn't ? If that is the case, both action_dt could be date-time format instead of just date format. Otherwise both expression are equivalent, furthermore, I would suspect '+1' has better performance as it is the simpler operation.

Re: Join on offsetting dates

Ksharp — Tue, 10 Mar 2015 08:51:41 GMT

What if there was not always +1?

data A ;

input CM15 case_nbr $ Action_dt : mmddyy12.;

format Action_dt mmddyy10.;

cards;

1234 A1234 1/1/2015

1235 A1235 1/12/2015

1236 A1236 1/20/2015

1237 A1237 1/20/2015

1238 A1238 1/23/2015

;

run;

data B ;

input CM15 action_dt : mmddyy12.;

format Action_dt mmddyy10.;

cards;

1234 1/1/2002

1234 1/2/2015

1234 1/30/2015

1235 1/13/2015

1235 2/1/2015

1236 1/20/2015

1237 1/21/2015

1238 2/6/2015

;

run;

proc sql;

create table want(drop=dif) as

select a.*,b.action_dt as action_dt_2 ,(b.Action_dt-a.Action_dt) as dif

from a left join b on a.CM15=b.CM15

where calculated dif gt 0 or calculated dif is missing

group by a.CM15

having calculated dif=min(calculated dif);

quit;

Xia Keshan