intervalds and INTCK not counting when starting out of bound

[JM05]

Hi everyone,

I'm trying to calculate the delay between 2 timestamps with a custom interval that represent business hours. (Entreprise Sas guide 7.1)

My issue : if the starting or the ending timestamps is out of the business hours (not included in my custom interval), it doesn't calculate the delay for the time included in the custom interval and return a null value.

Is there a way to have a custom interval, with intervalds, that work with variable starting or ending outside the interval?

Thank you for the support

For the custom interval :

data StoreHour(keep=BEGIN END);
    start = '01JAN2019'D;
    stop = '31DEC2021'D;
     do date = start to stop;
         dow = WEEKDAY(date);
         datetime=dhms(date,0,0,0);
          if dow not in (1,7) then
             do minute = 510 to 1110;   /*8:30 to 18:30 */
                    begin=intnx('minute',datetime,minute,'b');
                    end=intnx('minute',datetime,minute,'e');
  output;
  end;
end;
format BEGIN END DATETIME.;
run;

Example of code for testing :

options intervalds=(StoreHours=StoreHour); 

data step1;
    TIME_START = '27NOV2019:16:24:58.732616'dt;
    MAJTIMESTAMP0 = '27NOV2019:23:59:00.000000'dt;
    DELAI_M = INTCK('StoreHours',TIME_START,MAJTIMESTAMP0); 
    DELAI_H = round(DELAI_M/60,.01);
run;

Result :

TIME_START       MAJTIMESTAMP0   DELAI_M   DELAI_H
1890491098.7      1890518340                 .                 .

data step2;
    TIME_START = '27NOV2019:16:24:58.732616'dt;
    MAJTIMESTAMP0 = '27NOV2019:16:59:00.000000'dt;
    DELAI_M = INTCK('StoreHours',TIME_START,MAJTIMESTAMP0); 
    DELAI_H = round(DELAI_M/60,.01);
run;

 

Result:

TIME_START      MAJTIMESTAMP0    DELAI_M   DELAI_H
1890491098.7     1890493140                35             0.58

[JM05 · Accepted solution]

I had to move forward with the request, so I made a work around : 

if weekday(datepart(MAJTIMESTAMP0)) = 7 
		then MAJTIMESTAMP0 = dhms(datepart(MAJTIMESTAMP0)-1,18,30,00);
	if weekday(datepart(MAJTIMESTAMP0)) = 1 
		then MAJTIMESTAMP0 = dhms(datepart(MAJTIMESTAMP0)-2,18,30,00);
	if timepart(MAJTIMESTAMP0) >= '00:00:00't and timepart(MAJTIMESTAMP0) < '08:30:00't
		then MAJTIMESTAMP0 = dhms(datepart(MAJTIMESTAMP0),8,30,00);
	if timepart(MAJTIMESTAMP0) >= '18:30:00't and timepart(MAJTIMESTAMP0) <= '24:00:00't
		then MAJTIMESTAMP0 = dhms(datepart(MAJTIMESTAMP0),18,30,00);

I change the saturday and sunday to the previous friday and I change the timestamp to be at the begenning or the ending of the custom interval. 

It's not the perfect solution I was wishing but it's working 🙂