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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: