proc sql join where date = date + 1 day

[DartRodrigo]

Hi mates,

I'm trying to make a join where some of my dates are as follows:

date of table 1 = 12apr2015:00:23:46

date of table 2 = 13apr2015:00:23:46

I'd like to join these table in condition just like d1.date = d2.date + 1*24*60*60(one more day).

But consider that some dates are the same in both tables but sometimes they don't.

In fact the join should match in both cases.

The code to test is this:

data have;
infile datalines4;
format date date9.;
input ID$     VISIT     value1     value2     value3   date:date9. ;
datalines4; 
A     1     5     7     9   16jan2015
A     2     .     .      .  14feb2015
A     3     .     .      .  27mar2015
A     4     10     5     3  17apr2015
A     5     .     .      .  17may2015
B     1     11     2     6  18jun2015
B     2     .      .     .  19jul2015
B     3     16     7     9  10aug2015
B     4     .      .     .  11sep2015
B     5     18     20     5 17oct2015
;;;;;;
run;

%macro date(num);
data want;
set have;
  %do i =1 %to 3;
    if value&&i. = . then value&&i.=0;
  %end;
run;
%mend;
%date(3);

data last;
   infile datalines4;
   format date date9.;
input x$  date:date9.;
datalines4;
A 17jan2015
A 15feb2015
A 28mar2015
A 18apr2015
A 18may2015
B 19jun2015
B 20jul2015
B 11aug2015
B 12sep2015
B 17oct2015
;;;;;;
run;

proc sql;
   create table final as 
      select *
				from want as w 
	  			right join last as l on (w.date=l.date);
quit;

How can i fix this ?

Thanks before any answer

[PGStats]

Do you want to match SAS dates or SAS datetimes? Your question seems to be about datetimes but your example involves only SAS dates. 

[DartRodrigo]

Datetimes

Tks

[Steelers_In_DC]

For the example data you have the solution will work.  I didn't test with datetime but if the real data is datetime I would try the commented out line.  If the dataset is very large it might be better add a new variable with the datepart already derived because the join will do this over and over again for every record:

proc sql;
create table final as
select a.*,b.date as date_b
from want a inner join
     last b on
/*a.date = intnx('day',datepart(b.date),-1,'same');*/
a.date = b.date -1;

[PGStats · Accepted solution]

I rebuilt your example so that it involves datetimes only. It is not clear whether you want/need to match times exactly. I assumed not:

data have;
format date datetime.;
input ID$     VISIT     value1     value2     value3   d:date9. ;
date = dhms(d,0,0,0);
drop d;
datalines; 
A     1     5     7     9   16jan2015
A     2     .     .      .  14feb2015
A     3     .     .      .  27mar2015
A     4     10     5     3  17apr2015
A     5     .     .      .  17may2015
B     1     11     2     6  18jun2015
B     2     .      .     .  19jul2015
B     3     16     7     9  10aug2015
B     4     .      .     .  11sep2015
B     5     18     20     5 17oct2015
;


data last;
format date datetime.;
input ID$  d:date9.;
date = dhms(d,0,0,0);
drop d;
datalines;
A 17jan2015
A 15feb2015
A 28mar2015
A 18apr2015
A 18may2015
B 19jun2015
B 20jul2015
B 11aug2015
B 12sep2015
B 17oct2015
;

proc sql;
create table final as 
select h.*, last.date as otherDate
from
    have as h 
    left join last as l 
        on  h.id = l.id and
            intck("DTDAY", h.date, l.date) between 0 and 1;
select * from final;
quit;

[DartRodrigo]

Pg, thanks, it wokrs very well.

But i have one question, i work in a bank and the table i'm using has some transactions that occur in same time of the other.

Just like:

A Guy make a transaction in 12aug205:12:45:00

And then the same guy make another transaction with same value and datetime of the first.

Would those lines be duplicated ?

Tks

[PGStats]

If you want to spot only the datetime pairs involving successive days then use 

intck("DTDAY", h.date, l.date) = 1

but remember, this condition is true for all pairs of transactions on successive days (irrespective of the time of day). If the times must match exactly, then you should use

l.date - h.date = '24:00:00't