Help using Base SAS procedures

Join on offsetting dates

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Join on offsetting dates

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

CM15case_nbrAction_dt
1234A12341/1/2015
1235A12351/12/2015
1236A12361/20/2015
1237A12371/20/2015
1238A12381/23/2015

  

Table B

CM15action_dt
12341/1/2002
12341/2/2015
12341/30/2015
12351/13/2015
12352/1/2015
12361/20/2015
12371/21/2015
12382/6/2015


Expected Results

CM15case_nbraction_dt2nd_action_dt
1234A12341/1/20151/2/2015
1235A12351/12/20151/13/2015
1237A12371/20/20151/21/2015



Accepted Solutions
Solution
‎03-09-2015 04:49 PM
Super Contributor
Posts: 578

Re: Join on offsetting dates

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;

View solution in original post


All Replies
Super Contributor
Posts: 275

Re: Join on offsetting dates

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;

Solution
‎03-09-2015 04:49 PM
Super Contributor
Posts: 578

Re: Join on offsetting dates

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;

New Contributor
Posts: 2

Re: Join on offsetting dates

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

Respected Advisor
Posts: 3,156

Re: Join on offsetting dates

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.

Super User
Posts: 10,046

Re: Join on offsetting dates

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 213 views
  • 0 likes
  • 5 in conversation