Excluding Weekends and Holidays in SAS EG

[Tajinder]

Hi All,

Being a non programmer I have started using SAS EG tool. I have below data set from SAS, I need to exclude Holidays/Weekends from this start date and end date to arrive at actual working hours.. The number of hours in a day I want to keep as 9.30 am to 6.30 pm..Weekends would be Saturday and Sunday.e.g. If any of the day falls in between i.e. start date is say 7th Sept  18 (Friday) and end Date is 13th Sept 18 (Thursday) while I do a minus of these two dates to arrive at number of hours, system should ignore 8th and 9th Sept since these are weekends (saturday and sunday)... pls help with any function or a small program which I can embedd in the computed columns (advanced expresssion option).Pls look through the format here as it is a combination of date and time

InTime	OutTime
15May2018:4:02:00.00 PM	17May2018:7:37:00.00 PM
17May2018:7:37:00.00 PM	18May2018:6:26:00.00 PM
18May2018:6:26:00.00 PM	19May2018:2:06:00.00 PM
19May2018:2:06:00.00 PM	22May2018:10:30:00.00 AM
22May2018:10:30:00.00 AM	22May2018:10:52:00.00 AM
22May2018:10:52:00.00 AM	22May2018:12:03:00.00 PM
22May2018:10:52:00.00 AM	22May2018:12:09:00.00 PM
22May2018:12:09:00.00 PM	22May2018:1:07:00.00 PM
22May2018:12:09:00.00 PM	22May2018:4:01:00.00 PM
22May2018:12:09:00.00 PM	22May2018:6:30:00.00 PM
22May2018:12:09:00.00 PM	23May2018:11:24:00.00 AM
23May2018:11:24:00.00 AM	23May2018:11:43:00.00 AM
16Aug2018:1:37:00.00 PM	16Aug2018:3:21:00.00 PM
16Aug2018:3:21:00.00 PM	16Aug2018:6:55:00.00 PM
16Aug2018:6:55:00.00 PM	18Aug2018:12:22:00.00 PM
16Aug2018:6:55:00.00 PM	24Aug2018:11:05:00.00 AM
24Aug2018:11:05:00.00 AM	24Aug2018:2:55:00.00 PM
24Aug2018:11:05:00.00 AM	24Aug2018:6:19:00.00 PM
24Aug2018:11:05:00.00 AM	24Aug2018:7:51:00.00 PM
24Aug2018:11:05:00.00 AM	25Aug2018:12:38:00.00 PM

[Tajinder]

Dear All, pls help it is bit urgent ! An early response would be highly appreciated

[TomKari]

I'm having a hard time thinking of a way to do this in Query Builder. Could you use a program in a code window instead?

Tom

[Tajinder]

Thanks Tom for the reply..

I can use the option of writing an advanced expression for a computed column ..attaching the screen short for what I am trying to tell.

This is the window where I can put the SAS code .. pls guide

or if you can share the program code, pls help me how do i get that computed field to my existing table in SAS EG

[TomKari]

To make sure I understand, I've explored a few of your records:

22MAY18:10:52:00 (Tuesday) to 22MAY18:12:09:00

You would want the time from 10:52 to 12:09 (around an hour and a half).

15MAY18:16:02:00 (Tuesday) to 17MAY18:19:37:00

You would want the time from 16:02 to 6:30 PM (18:30) on the 15th (Tuesday), the time from 9:30 AM to 6:30 PM on the 16th (Wednesday), and the time from 9:30 AM to 6:30 PM (18:30) on the 17th (Thursday).

24AUG18:11:05:00 (Friday) to 25AUG18:12:38:00

You would want the time from 11:05 to 6:30 PM (18:30) on the 24th (Friday), and no time on the 25th (Saturday).

Let me know if this looks correct.

[Tajinder]

Yes Tom.Your understanding is 100 % correct. For Saturday case which is 25th Aug as you highlghted, it is good to have 3rd and 5th Saturday of the Month. If this can be doable.Otherwise we can keep Sunday only as week holiday..

Appreciate Tom that you help the community with all your best efforts

[TomKari]

Here's a piece of code that tries to do what you want. To use it:

1. Open a code window (File -> New -> Program). This will open a window for you to create a SAS program.

2. Copy and paste the code from the window below.

3. The second line says "set Have". Change Have to the name of your input dataset (what you would run the query against.)

4. The 6th line from the bottom says "create table Want as". Change Want to the name of the dataset you want to create (the Output Name you would use in your query).

5. Save the project, and run the program by clicking on the Run button.

Make sure you do lots of testing! These types of requirements are tricky to get right.

Tom

data Inter01;

	set Have;

	intimedate = datepart(intime);
	intimetime = timepart(intime);
	format intimedate date.;
	format intimetime time.;

	outtimedate = datepart(outtime);
	outtimetime = timepart(outtime);
	format outtimedate date.;
	format outtimetime time.;

	if intimetime < "09:30:00"t
	then starttime = "09:30:00"t;
	else if intimetime > "18:30:00"t
		then starttime = "18:30:00"t;
		else starttime = intimetime;

	if outtimetime > "18:30:00"t
	then finishtime = "18:30:00"t;
	else if outtimetime < "09:30:00"t
		then finishtime = "09:30:00"t;
		else finishtime = outtimetime;

	format starttime finishtime time.;

	do processdate = intimedate to outtimedate;
		format processdate date.;
		outputstarttime = starttime;
		outputfinishtime = finishtime;
		format outputstarttime outputfinishtime time.;

		if intimedate = outtimedate /* both times on a single day */
		then;
		else do;

			/* spans over at least one day */
			if intimedate < processdate /* A day later than the first day */
			then outputstarttime = "09:30:00"t;

			if processdate < outtimedate /* A day earlier than the last day */
			then outputfinishtime = "18:30:00"t;

			if weekday(processdate) in (1, 7) /* Sunday or Saturday */
			then do;
				outputstarttime = "00:00:00"t;
				outputfinishtime = "00:00:00"t; /* No duration for this day */
			end;
		end;

		output;
	end;
run;

data Inter02;
	set Inter01;
	duration = outputfinishtime - outputstarttime;
	format duration time.;
run;

proc means data=Inter02 noprint nway;
	var duration;
	class inputrecordnumber;
	output out=Inter03 sum(duration)=totalduration;
run;

proc sql noprint;
	create table Want as 
		select h.intime, h.outtime, i.totalduration
			from Have h inner join Inter03 i
				on h.inputrecordnumber = i.inputrecordnumber
			order by h.inputrecordnumber;
quit;

[Tajinder]

Hi Tom,

Thanks for the program. Could not attend office due to some personal exigencies. I have executed the below program just now. However after reading the data set , it got stuck for Error : "ERROR: Variable INPUTRECORDNUMBER not found."

Attaching logs for your reference.Pls advice.

MY SAS version is : 7.11 HF5 (7.100.1.2856) (64-bit)

[Tajinder]

Hi Tom,

have replaced the "have" in the last program of create table  with the actual data name as I passed in the first line of Set command. Still the same error

Also if you help me to add Holidays in the program so as to ignore in calculation besides Sundays. I can create a data in the sas with two columns.. Date and Holiday name and then upload in my project which can be referred to / link in your given program

An early reply would be highly appreciated... Thanking you in advance

[TomKari]

Sorry, dopey mistake on my part. I forgot that you weren't going to run the program that creates "Have", and that's where I stuck the line.

Add these lines at the top of your program:

data Inter01a;
set Base_data;
inputrecordnumber = _n_;

run;

and then in your first program, that starts with "data Inter01;", replace

set Base_data;

with

set Inter01a;

Also, in the program that starts "proc sql noprint;":

1. You need to change

create table work.Base_data_without_weekends

to

create table work.Base_data_without_weekends as

2. You need to change

from Have h inner join Inter03 i

to

from Inter01a h inner join Inter03 i

That's another mistake on my part, I'm sad to say.

Let's get this going, then we'll worry about holidays.

Tom

[Tajinder]

Hi Tom checked with few cases one with Sunday and one without Sunday as holiday.. Answers are perfect !..Thank you.. Await to hear from you on the remaining piece of Holidays. Today is my last day to confirm whether it is doable on SAS or not otherwise I have to switch to xls..pls help with the last leg asap..i.e. Holiday handling

[TomKari · Accepted solution]

Okay, give this a try.

At the top of the program, put in the following lines:

data Holidays;
informat Holiday date9.;
format Holiday date9.;
input Holiday;
HolidayFlag = 1;
cards;
18may2018
15jun2018
16aug2018
run;

and change them for the dates to represent your holidays.

After the step that begins "data Inter01;", put in the following lines (right after the "run;" line that ends the step). They will set the hours for holidays to zero.

proc sql noprint;
create table Inter01b as
select i1.*, h.HolidayFlag
from Inter01 i1 left join Holidays h
on i1.processdate = h.Holiday;
quit;

data Inter01c;
set Inter01b;
if HolidayFlag = 1 /* Holiday */
then do;
outputstarttime = "00:00:00"t;
outputfinishtime = "00:00:00"t; /* No duration for this day */
end;
run;

and finally, in the step that begins "data Inter02;", change then line

set Inter01;

to

set Inter01c;

to pick up the dataset that has the holidays excluded.

Tom

[Tajinder]

Thank You TOM !