BookmarkSubscribeRSS Feed
hellind
Quartz | Level 8

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.

8 REPLIES 8
Patrick
Opal | Level 21

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.

hellind
Quartz | Level 8

Found out  my company is using version 9.1.3

Imroze
Fluorite | Level 6

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

Patrick
Opal | Level 21

I believe you forgot about weekends and especially holidays. You will need some sort of calendar for this.

hellind
Quartz | Level 8

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?

art297
Opal | Level 21

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

hellind
Quartz | Level 8

Thanks, I had read those links. I am in Asia, and my company uses SAS 9.1.3.Smiley Happy

Using INTERVALDS would had been great but this is not suppored in SAS 9.1.3

I may have to use loops in datastep.

Imroze
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 2439 views
  • 2 likes
  • 4 in conversation