topic Re: Proc sql joint with interval between 2 dates in SAS Programming

Proc sql joint with interval between 2 dates

SASdevAnneMarie — Wed, 18 Oct 2023 16:22:38 GMT

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 !

Re: Proc sql joint with interval between 2 dates

PaigeMiller — Wed, 18 Oct 2023 16:28:49 GMT

@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

Re: Proc sql joint with interval between 2 dates

Kurt_Bremser — Wed, 18 Oct 2023 17:09:22 GMT

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

and a.D_2 - b.D_1 = 7

Re: Proc sql joint with interval between 2 dates

Tom — Thu, 19 Oct 2023 03:05:36 GMT

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.

Re: Proc sql joint with interval between 2 dates

SASdevAnneMarie — Thu, 19 Oct 2023 05:40:59 GMT

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?

Re: Proc sql joint with interval between 2 dates

Kurt_Bremser — Thu, 19 Oct 2023 05:44:51 GMT

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