Join on offsetting dates

[Sotark]

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

[slchen]

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;

[DBailey · Accepted solution]

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;

[Sotark]

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

[Haikuo]

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.

[Ksharp]

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