Proc sql joint with interval between 2 dates

[SASdevAnneMarie]

Hello Experts,

My Date_1 < Date_2 so I would like to get only the observation with 7 days diffrence. Could you tell me if my code is right :

proc sql;
	create table Result as select a.*, b.* from total_RE as a
		left join SUP as b on a.pers=b.pers
			and intck('day',b.D_1, a.D_2)<7;
quit;

Thank you !

[PaigeMiller]

---

@SASdevAnneMarie wrote:

My Date_1 < Date_2 so I would like to get only the observation with 7 days diffrence. Could you tell me if my code is right :

---

Did you actually try running this code? SAS will tell you if the code is right, and do so more quickly and more authoritatively than anyone here can do (especially since we don't have your data).

There do not appear to me to be any syntax errors (but again SAS will give you are more authoritative answer). I question the logic in the code which doesn't seem to match the words you used; 7 days difference would be 

intck('day',b.D_1, a.D_2)=7

[Kurt_Bremser]

Since dates in SAS are counts of days, the INTCK call is not necessary.

and a.D_2 - b.D_1 = 7

[Tom · Accepted solution]

Exactly 7 days?  7 days or less?  Or less than 7 days?

Assuming your date variables have DATE values then they are already stored as a number of days, so there is no need to use any special functions to check the differences.

To test if D2 is between D1 and up to 7 days after D1 just use:

proc sql;
create table Result as 
  select a.*, b.* 
  from total_RE as a
  left join SUP as b
    on a.pers=b.pers
   and a.D_2 between b.D_1 and b.D_1+7
;
quit;

If you actually have DATETIME values then you would need to use INTNX() or INTCK() since those values are stored as number of seconds.  But you would need to use the DTDATE interval and not the DATE interval.

[SASdevAnneMarie]

Thank you very much!
I have datetime values and I would like to chose 7 days or less interval. How to indicate the datetime interval please?

[Kurt_Bremser]

Then you have to use INTNX or INTCK with a DTDAY interval.