topic Re: intck('hours') how to exclude certain time and day? in SAS Procedures

intck('hours') how to exclude certain time and day?

hellind — Fri, 24 Jan 2014 04:15:23 GMT

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.

Re: intck('hours') how to exclude certain time and day?

Patrick — Fri, 24 Jan 2014 06:59:45 GMT

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.

Re: intck('hours') how to exclude certain time and day?

hellind — Fri, 24 Jan 2014 07:40:42 GMT

Found out my company is using version 9.1.3

Re: intck('hours') how to exclude certain time and day?

Imroze — Fri, 24 Jan 2014 10:14:38 GMT

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

Re: intck('hours') how to exclude certain time and day?

Patrick — Fri, 24 Jan 2014 10:57:21 GMT

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

Re: intck('hours') how to exclude certain time and day?

hellind — Tue, 28 Jan 2014 02:21:56 GMT

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?

Re: intck('hours') how to exclude certain time and day?

art297 — Tue, 28 Jan 2014 03:14:25 GMT

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

Re: intck('hours') how to exclude certain time and day?

hellind — Tue, 28 Jan 2014 04:50:44 GMT

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.

Re: intck('hours') how to exclude certain time and day?

Imroze — Tue, 28 Jan 2014 07:12:06 GMT

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