I am trying to calculate SLA in hours.
And this is the logic:
Work start time: 9am
Work end time: 4pm
exclude public holidays and weekends
At 4pm the hourly clock stops.
For example:
An application is submitted at 1pm on 2nd Jan 2014, and now it is 10am 3rd Jan, then SLA is 4 hours (1pm to 4pm on 2nd Jan, and then 9am to 10 am on 3rd Jan)
Another application is submitted at 5pm on 2nd Jan 2014, and now it is 10am 3rd Jan, then SLA is 1 hour ( 9am to 10 am on 3rd Jan)
How do I do it easily, other than using data step if statements?
There are many ways.
Investigate in the SAS doc how custom intervals can get created and used. The examples in the doc (or was it a white paper?) are very close to what you are after.
Found out my company is using version 9.1.3
Hope the below code helps you by changing few variables:::
data _null_;
length x $20.;
retain slastart 0;
day=date();
time=time();
slastrdt=dhms(day,0,0,time);
slaenddt=dhms(sum(day,1),0,0,time);
hrstr=min(max(hour(slastrdt),9),16);
hrend=min(max(hour(slaenddt),9),16);
/****please change this*****/
dtdiff=intck('day',day,sum(day,1));
/****************************/
select(hrstr);
when(9) hrstr=7;
when(10) hrstr=6;
when(11) hrstr=5;
when(12) hrstr=4;
when(13) hrstr=3;
when(14) hrstr=2;
when(15) hrstr=1;
when(16) hrstr=0;
otherwise put"error occured";
end;
select(hrend);
when(9) hrend=0;
when(10) hrend=1;
when(11) hrend=2;
when(12) hrend=3;
when(13) hrend=4;
when(14) hrend=5;
when(15) hrend=6;
when(16) hrend=7;
otherwise put"error occured";
end;
if dtdiff eq 1 then sla=sum(hrstr,hrend);
else if dtdiff ge 2 then sla=sum(hrstr,hrend,(dtdiff-1)*7);
put sla=;
run;
Thank you
Thanks. I drew inspiration from your code. I got what I want but now I need to exclude public holidays. My approach will be to maintain a list of public holidays in a CSV and import it to a data-set.
DATA HOLIDAYS;
INPUT NAME $ HOLIDATE ;
DATALINES;
CHRISTMAS 25-DEC-2013
NEW YEAR 01-JAN-2014
;
How could I incorporate the HOLIDAYS table to the above code to minus 24 hours from SLA?
If you only need the standard US and/or Canadian holidays, you can incorporate the holiday function:
SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition
for other holidays, you can use: Sometimes One Needs an Option with Unusual Dates - sasCommunity
Thanks, I had read those links. I am in Asia, and my company uses SAS 9.1.3.
Using INTERVALDS would had been great but this is not suppored in SAS 9.1.3
I may have to use loops in datastep.
Hi hellind
As the requirement seems to be a support based do you guys work on weekends and holidays.
Or else we can include few conditions like weekday() for discarding weekends support.
Please find the updated code below... hope it helps
data _null_;
length x $20.;
retain slastart 0;
/***start of sla request***/
day=sum(date(),1);
time=time();
/***ending the sla****/
enddate=sum(day,3);
endtime=time();
slastrdt=dhms(day,0,0,time);
slaenddt=dhms(enddate,0,0,time);
/***************WEEK DAY Processing***************/
weekdy=weekday(day);
if weekdy ge 2 and weekdy le 6 then
dtdiff=intck('day',day,sum(day,1));
else if weekdy in (1,7) then do;
select(weekdy);
when(1) if weekday(enddate) not in (1,7) then do;
dtdiff=intck('day',day,sum(day,1))-1;
end;
when(7) if weekday(enddate) not in (1,7) then do;
dtdiff=intck('day',day,sum(day,1))-2;
end;
otherwise put "error occured";
end;
end;
/***************WEEK DAY Processing***************/
hrstr=min(max(hour(slastrdt),9),16);
hrend=min(max(hour(slaenddt),9),16);
/****please change this*****/
dtdiff=intck('day',day,sum(day,1));
/****************************/
select(hrstr);
when(9) hrstr=7;
when(10) hrstr=6;
when(11) hrstr=5;
when(12) hrstr=4;
when(13) hrstr=3;
when(14) hrstr=2;
when(15) hrstr=1;
when(16) hrstr=0;
otherwise put"error occured";
end;
select(hrend);
when(9) hrend=0;
when(10) hrend=1;
when(11) hrend=2;
when(12) hrend=3;
when(13) hrend=4;
when(14) hrend=5;
when(15) hrend=6;
when(16) hrend=7;
otherwise put"error occured";
end;
if dtdiff eq 1 then sla=sum(hrstr,hrend);
else if dtdiff ge 2 then sla=sum(hrstr,hrend,(dtdiff-1)*7);
put sla=;
run;
Thank You
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.