BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tparvaiz
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

15 REPLIES 15
Reeza
Super User

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

 

tparvaiz
Obsidian | Level 7

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

 

 

 

 

 

 

 

Reeza
Super User

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. 

tparvaiz
Obsidian | Level 7

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

 

Reeza
Super User

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

tparvaiz
Obsidian | Level 7

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

 

 

Reeza
Super User

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

 

tparvaiz
Obsidian | Level 7

Thanks again for your assistance

art297
Opal | Level 21

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

 

Ron_MacroMaven
Lapis Lazuli | Level 10
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
art297
Opal | Level 21

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

tparvaiz
Obsidian | Level 7

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

 

 

Reeza
Super User

Your variables are DATETIME not DATE variables. 

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

tparvaiz
Obsidian | Level 7
it worked with following change

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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