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 |
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;
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;
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;
Thank you DBailey. It appears I had the right thought process, just the wrong syntax? Using the intnx did exactly what I needed.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.