Help using Base SAS procedures

proc sql join where date = date + 1 day

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

proc sql join where date = date + 1 day

[ Edited ]

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


Accepted Solutions
Solution
‎10-23-2015 02:11 PM
Respected Advisor
Posts: 4,920

Re: proc sql join where date = date + 1 day

Posted in reply to DartibaliRodrigo

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;
PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,920

Re: proc sql join where date = date + 1 day

Posted in reply to DartibaliRodrigo

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

PG
Regular Contributor
Posts: 212

Re: proc sql join where date = date + 1 day

Datetimes

 

Tks

Valued Guide
Posts: 860

Re: proc sql join where date = date + 1 day

Posted in reply to DartibaliRodrigo

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;

Solution
‎10-23-2015 02:11 PM
Respected Advisor
Posts: 4,920

Re: proc sql join where date = date + 1 day

Posted in reply to DartibaliRodrigo

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;
PG
Regular Contributor
Posts: 212

Re: proc sql join where date = date + 1 day

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

Respected Advisor
Posts: 4,920

Re: proc sql join where date = date + 1 day

Posted in reply to DartibaliRodrigo

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

 

 

PG
🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 789 views
  • 2 likes
  • 3 in conversation