topic Re: macro to exclude week end days and company holidays from the count of days in SAS Programming

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

tparvaiz — Thu, 04 May 2017 21:44:29 GMT

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

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

Reeza — Thu, 04 May 2017 21:49:43 GMT

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_sect008.htm

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

art297 — Thu, 04 May 2017 21:56:03 GMT

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

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

Ron_MacroMaven — Thu, 04 May 2017 22:33:17 GMT

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

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

tparvaiz — Tue, 09 May 2017 21:42:51 GMT

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

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

Reeza — Tue, 09 May 2017 21:44:30 GMT

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.

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

tparvaiz — Tue, 09 May 2017 22:52:16 GMT

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

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

Reeza — Tue, 09 May 2017 23:12:51 GMT

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

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

tparvaiz — Wed, 10 May 2017 00:56:32 GMT

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

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

art297 — Wed, 10 May 2017 03:29:32 GMT

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

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

Reeza — Wed, 10 May 2017 03:40:45 GMT

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

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

tparvaiz — Wed, 10 May 2017 17:08:38 GMT

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

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

Reeza — Wed, 10 May 2017 17:32:50 GMT

Your variables are DATETIME not DATE variables.

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

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

tparvaiz — Wed, 10 May 2017 17:35:21 GMT

it worked with following change

workdays = intck('workdays', datepart(start_date), datepart(end_date));

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

Reeza — Wed, 10 May 2017 17:40:54 GMT

Then please mark @art297 solution as the correct answer.

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

tparvaiz — Wed, 10 May 2017 20:28:58 GMT

Thanks again for your assistance