good day,
I need to calculate number of work days between 2 given dates (start and the end). I need to do a couple things
1) I want to exclude weekends (sat and sunday) from my count
2) and I want to exclude company holidays (6 days) from my count
as an example, if the start-date is May 1 2017 and the end-date is may may 19 2017. Assuming that May 4 and May 5 are company holidays, then I want the macro to return a count of 13... here is how it should count
M T W T F S S
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19
Please advise
Try the following:
data wrkdays (keep=begin); format begin date9.; array holidays(6); do date = '01jan1971'd to '31dec2070'd ; if date eq intnx('year',date,0,'b') then do; call missing(of holidays(*)); i=0; end; if date eq holiday('NEWYEAR', year(date)) or date eq holiday('USINDEPENDENCE',year(date)) or date eq holiday('THANKSGIVING', year(date)) or date eq holiday('CHRISTMAS', year(date)) or date eq holiday('MEMORIAL', year(date)) or date eq holiday('LABOR', year(date)) then do; i+1; holidays(i)=date; end; if not(date in holidays or weekday(date) in (1,7)) then do; season=1; begin=date; output; end; end; run; options intervalds=(workdays=wrkdays); data test; days=intck('workdays','1may2017'd,'19may2017'd); run;
Art, CEO, AnalystFinder.com
I would recommend building a custom time interval instead.
Even if you don't have SAS/ETS this is still valid for INTNX(). See the latter example which uses INTCK (not INTNX).
Hi,
I've tried a few things and nothing worked so far. here is what I am trying to do
current query
proc sql;
select (date_end-date_start) as Calender_days
from
Table_A;
this query return calander days between start and the end date
I want the same query to now return only the work days minus Holidays (selected ones)... here is the script that I found to identify the holidays but I am unable to figure out the logic
data holidays ;
length HolidayName $ 30 ;
array WkDayShift [7] _temporary_ ( 1 5*0 -1 ) ;
retain ShiftToggle /* 0 */ 1 ;
do Year = 1971 to 2070 ;
HolidayName = "New Year's Day" ;
* 1 Jan if not Mon 2 Jan. ;
HoliDate = holiday('newyear', year) ;
dow = weekday(HoliDate) ;
HoliDate = intnx( 'day', HoliDate, WkDayShift[dow] * ShiftToggle ) ;
if not ( (dow EQ 7) and ShiftToggle ) then output ;
HolidayName = "Martin Luther King Day" ;
* 3rd Mon in Jan ;
HoliDate = holiday('mlk', year) ;
output ;
HolidayName = "Presidents' Day" ;
* 3rd Mon in Feb ;
HoliDate = holiday('uspresidents', year) ;
output ;
HolidayName = "Memorial Day" ;
* Last Mon in May ;
HoliDate = holiday('memorial', year) ;
output ;
HolidayName = "Independence Day" ;
* 4 Jul if not Mon 5 Jul or Fri 3 Jul ;
HoliDate = holiday('usindependence',year) ;
HoliDate = intnx('day',
HoliDate,
WkDayShift[ weekday(HoliDate) ] * ShiftToggle) ;
output ;
HolidayName = "Labor Day" ;
* 1st Mon in Sep ;
HoliDate = holiday('labor', year) ;
output ;
HolidayName = "Thanksgiving Day" ;
*4th Thu in Nov ;
HoliDate = holiday('thanksgiving', year) ;
output ;
HolidayName = "Christmas" ;
* 25 Dec if not Mon 26 Dec or Fri 24 Dec ;
HoliDate = holiday('christmas', year) ;
HoliDate = intnx('day',
HoliDate,
WkDayShift[ weekday(HoliDate) ] * ShiftToggle) ;
output ;
HolidayName = "New Year's Eve" ;
* Fri 31 Dec or not at all ;
HoliDate = mdy(12, 31, year) ;
if weekday(HoliDate) EQ 6 and ShiftToggle then output ;
end ;
keep year HolidayName HoliDate ;
format HoliDate weekdate17. ;
run ;
proc print data=holidays ;
where year in (2017, 2018) ;
by year ;
id year ;
run ;
Thanks in advance
Are you in the US? Which Holidays are you factoring in?
Please FORMAT YOUR CODE. Trying to read unformatted code is likely trying to read a sentence without any punctuation.
yes, I am in US and want to factor in following hollidys
Thanks
You created the holidays dataset but it doesn't show you trying to use it anywhere. Am I missing something here?
here is the code that I've used
%let date = feb2017;
%let date1 = '01feb2017'd;
%let date2 = '01mar2017'd;
options intervalds=(WorkDays=WorkDayDS);
data WorkDayDS(keep=BEGIN);
start = '15DEC1998'D;
stop = '15JAN2030'D;
nwkdays = INTCK('WEEKDAY',start_Date,End_date);
do i = 0 to nwkdays;
BEGIN = INTNX('WEEKDAY',start_Date,i);
year = YEAR(BEGIN);
if BEGIN ne HOLIDAY("NEWYEAR",year) and
BEGIN ne HOLIDAY("MEMORIAL",year) and
BEGIN ne HOLIDAY("USINDEPENDENCE",year) and
BEGIN ne HOLIDAY("LABOR",year) and
BEGIN ne HOLIDAY("THANKSGIVING",year) and
BEGIN ne HOLIDAY("CHRISTMAS",year) then
output;
end;
format BEGIN DATE.;
run;
proc sql;
create table temp2
as
select *,
INTCK('WorkDays',Start_Date,End_date)/(60*60*24) as workdays
from DB.v_all_2017
where &date1 <= sched_dt < &date2;
-------------------------------
Issue 1: the output has 0 values for all the columns
Issue 2: if the holiday is on the weekend, then it's not switching... my other code that I've posted previously was switching the days perfectly but I don't know how to use it in the code above
Thanks
@art297 solution will not deal with #2, but to be honest, you should be able to modify the code. I generally don't recommend running code if you don't understand it.
I'm assuming you're referring to day in-lieu days - if XMAS is Sunday then Monday would be a day off as well. The idea seems clear - create a dataset that includes all the dates that would be working dates and then use that as Intervalds option. The variable must be named BEGIN and it needs to be the only variable in the dataset.
@tparvaiz Why did you divide the number of days by (60/60/24) in your SQL query? It returns the number of days.
Thanks again for your assistance
If your company holidays are always on weekdays, then you could get away with something as simple as:
data _null_; count=intck('weekdays','01may2017'd,'19may2017'd)-1; put count; run;
Art, CEO, AnalystFinder.com
Try the following:
data wrkdays (keep=begin); format begin date9.; array holidays(6); do date = '01jan1971'd to '31dec2070'd ; if date eq intnx('year',date,0,'b') then do; call missing(of holidays(*)); i=0; end; if date eq holiday('NEWYEAR', year(date)) or date eq holiday('USINDEPENDENCE',year(date)) or date eq holiday('THANKSGIVING', year(date)) or date eq holiday('CHRISTMAS', year(date)) or date eq holiday('MEMORIAL', year(date)) or date eq holiday('LABOR', year(date)) then do; i+1; holidays(i)=date; end; if not(date in holidays or weekday(date) in (1,7)) then do; season=1; begin=date; output; end; end; run; options intervalds=(workdays=wrkdays); data test; days=intck('workdays','1may2017'd,'19may2017'd); run;
Art, CEO, AnalystFinder.com
here is what I did... ran a code similar to the one below
data wrkdays (keep=begin); format begin date9.; array holidays(6); do date = '01jan1971'd to '31dec2070'd ; if date eq intnx('year',date,0,'b') then do; call missing(of holidays(*)); i=0; end; if date eq holiday('NEWYEAR', year(date)) or date eq holiday('USINDEPENDENCE',year(date)) or date eq holiday('THANKSGIVING', year(date)) or date eq holiday('CHRISTMAS', year(date)) or date eq holiday('MEMORIAL', year(date)) or date eq holiday('LABOR', year(date)) then do; i+1; holidays(i)=date; end; if not(date in holidays or weekday(date) in (1,7)) then do; season=1; begin=date; output; end; end; run; options intervalds=(workdays=wrkdays); data test;
set DB.v_all_2017 (where = (&date1 <= sched_dt < &date2));
days =intck('workdays','1may2017'd,'19may2017'd);
workdays = intck('workdays', start_date, end_date); run;
-----issue ----------------------------------------------
the count of days are coming just fine but the workdays field is showing 0 values for all the fields
----Output ---------------------------------------------
my table looks something like this (with workdays showing 0 values)... it works when I pass a fixed date value
Table: v_all_2017 | |||
start_date | end_date | workdays | days |
24JAN2017:09:45:00.000 | 08FEB2017:15:30:00.000 | 0 | 14 |
28JAN2017:00:35:00.000 | 08FEB2017:17:30:00.000 | 0 | 14 |
03FEB2017:08:49:10.940 | 07FEB2017:14:35:00.000 | 0 | 14 |
Please adviase
Your variables are DATETIME not DATE variables.
Use DATEPART() to convert them to DATE type to pass to the INTCK function.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.