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:
... View more