INTNX & INTCK using Custom Workday Intervals

[VictorSirianni]

Hello,

I've been asked to measure workdays between event timestamps according to a variety of business rules. To accomplish this I am trying to create custom intervals that will allow me to measure from the start of the next workday to the end of the last workday and then subtract out non-workdays between them using INTNX & INTCK functions.

/*** concept ***/

first_workday_dt = intnx('workday',start_timestamp,+1,'beginning');
final_workday_dt = intnx('workday',stop_timestamp,-1,'end');

workday_minutes = intck('dtminutes',first_workday_dt,final_workday_dt) - (intck('non_workdays',first_workday_dt,final_workday_dt)*1440);

I have a dataset containing all dates for the current and previous three years with a flag indicating weekends and holidays. From this I've created two new datasets with begin and end times for workdays and non-workdays. Creating intervals from these allows me to perform the calculations described above, however, I am seeing null results when the input date is not in the interval dataset or incorrect results when the result would extend beyond the interval dataset. While this makes sense intuitively, I am at a loss as to how to fix or work around it.

Is what I am trying to do possible, and if so, how? I am also open to other approaches as well, we are just trying to find the most efficient and repeatable way to do this as it is a common ask. Thank you in advance!

Code:

/*** flag weekends and holidays ***/
data work.Calendar (keep=date flag); merge work.Days work.Holidays (in=holiday); by date;

  format Date date. Flag $10.;

  if weekday(date) in (1,7) then flag = 'Weekend';
    else if holiday = 1 then flag = type;
      else flag = '';

run;


/*** create sas interval datasets ***/
proc sql;
create table work.Calendar_Workdays as select dhms(date,00,00,00) as begin format = datetime., dhms(date,23,59,59) as end format = datetime. from work.Calendar where flag is missing;
create table work.Calendar_NonWorkdays as select dhms(date,00,00,00) as begin format = datetime., dhms(date,23,59,59) as end format = datetime. from work.Calendar where flag is not missing;
quit;

/*** create sas intervals ***/
options intervalds = (workday=work.calendar_workdays non_workday=work.calendar_nonworkdays);

/*** advance to next workday and back to previous workday ***/
data work.test; set work.days;
format next_business_day last_business_day datetime.;
  next_business_day = intnx('workday',dhms(date,12,00,00),+1,'beginning');
  last_business_day = intnx('workday',dhms(date,12,00,00),-1,'end');
run;

Results:

[mkeintz]

Your calendar data sets have gaps, i.e. instances in which END for one observation is more than 1 unit (1 second) less than BEGIN for the next observation.

You could create END as next BEGIN-1 (much easier with a data step than in sql).  But you can also just drop the END variable.  Then sas will assume that end is just 1 less than the next begin, yielding no gaps.