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 |
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
Dear All, pls help it is bit urgent ! An early response would be highly appreciated
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
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
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.
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
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;
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)
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
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
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
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
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 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: