DATA Step, Macro, Functions and more

macro to exclude week end days and company holidays from the count of days

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 134
Accepted Solution

macro to exclude week end days and company holidays from the count of days

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


Accepted Solutions
Solution
‎05-10-2017 02:03 PM
PROC Star
Posts: 7,474

Re: macro to exclude week end days and company holidays from the count of days

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

View solution in original post


All Replies
Super User
Posts: 19,815

Re: macro to exclude week end days and company holidays from the count of days

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).

http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_intervals_sect...

 

Frequent Contributor
Posts: 134

Re: macro to exclude week end days and company holidays from the count of days

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

 

 

 

 

 

 

 

Super User
Posts: 19,815

Re: macro to exclude week end days and company holidays from the count of days

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. 

Frequent Contributor
Posts: 134

Re: macro to exclude week end days and company holidays from the count of days

yes, I am in US and want to factor in following hollidys

 

 

  • Memorial Day
  • Independence Day
  • Labor Day
  • Thanksgiving Day
  • Christmas  Day
  • New Year's Day

 

Thanks

 

Super User
Posts: 19,815

Re: macro to exclude week end days and company holidays from the count of days

You created the holidays dataset but it doesn't show you trying to use it anywhere. Am I missing something here?

Frequent Contributor
Posts: 134

Re: macro to exclude week end days and company holidays from the count of days

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

 

 

Super User
Posts: 19,815

Re: macro to exclude week end days and company holidays from the count of days

@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. 

 

Frequent Contributor
Posts: 134

Re: macro to exclude week end days and company holidays from the count of days

Thanks again for your assistance

PROC Star
Posts: 7,474

Re: macro to exclude week end days and company holidays from the count of days

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

 

Regular Contributor
Posts: 227

Re: macro to exclude week end days and company holidays from the count of days

check out this paper on making a report of activity by weeks

Pretty Dates All in a Row

Dianne Rhodes

www2.sas.com/proceedings/sugi31/015-31.pdf
Solution
‎05-10-2017 02:03 PM
PROC Star
Posts: 7,474

Re: macro to exclude week end days and company holidays from the count of days

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

Frequent Contributor
Posts: 134

Re: macro to exclude week end days and company holidays from the count of days

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

 

 

Super User
Posts: 19,815

Re: macro to exclude week end days and company holidays from the count of days

Your variables are DATETIME not DATE variables. 

Use DATEPART() to convert them to DATE type to pass to the INTCK function. 

Frequent Contributor
Posts: 134

Re: macro to exclude week end days and company holidays from the count of days

it worked with following change

workdays = intck('workdays', datepart(start_date), datepart(end_date));
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 252 views
  • 9 likes
  • 4 in conversation