DATA Step, Macro, Functions and more

exclude holidays from the count of days

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 134
Accepted Solution

exclude holidays from the count of days

good day, 

 

I am reposting this question as it's not working as per my requirements...

 

---------------------------------------------requirement ------------------------------------------------------------------------------------ 

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

 

 --------------------------------------------------Sample Code -----------------------------------------------------------------

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 temp;
set db.v_data (where = (&date1 <= sch_dt < &date2 ));

workdays = intck('workdays',datepart(Start_Date), datepart(End_date));

 

 

run;

 

 

-------------------------------------------------------Issue ------------------------------------------------

 

 

the above code is working fine for my requirement # 1, which is to exlcude weekend days from the count. BUT, it's not working for requirement #2, which is to exclude holidays from the count

 

 

please advise

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎05-15-2017 01:37 PM
PROC Star
Posts: 7,468

Re: exclude holidays from the count of days

I was wrong! Your code runs. I got confused because the indentation was off.

 

Does the following do what you want?

data wrkdays (keep=begin);
  format begin date9.;
  array holidays(12);
  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;
      if weekday(date) eq 1 then do;
        i+1;
        date+1;
        holidays(i)=date;
      end;
      else if weekday(date) eq 7 then do;
        i+1;
        date+1;
        holidays(i)=date;
        i+1;
        date+1;
        holidays(i)=date;
      end;
    end;
    if not(date in holidays or weekday(date) in (1,7)) then do;
      begin=date;
      output;
    end;
  end;
run;

options intervalds=(workdays=wrkdays);

libname db '/folders/myfolders';  

data db.v_data;
  infile cards dlm='09'x dsd;
  informat sch_dt start_date End_date anydtdtm.;
  input Customer_Name $	sch_dt	Start_date	End_date;
  cards;
Customer 1	23DEC2016:14:13:03.840	23DEC2016:14:13:03.840	14JAN2017:10:15:00.000
Customer 2	29DEC2016:10:10:23.637	29DEC2016:10:10:23.637	04JAN2017:10:45:00.000
Customer 3	22DEC2016:10:58:00.000	22DEC2016:10:58:00.000	06JAN2017:09:45:00.000
Customer 4	01may2017:11:07:50.910	01may2017:11:07:50.910	20may2017:08:00:00.000
;

%let date1=01may2017:00:00:00;
%let date2=19may2017:23:59:59.999;
data temp;
  set db.v_data (where = ("&date1"dt <= sch_dt < "&date2"dt ));
  workdays = intck('workdays',datepart(Start_Date), datepart(End_date));
run;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
PROC Star
Posts: 7,468

Re: exclude holidays from the count of days

[ Edited ]

I don't think any of us can help unless you provide enough information. You didn't provide an example have or want dataset, or the statements that create the macro variables you reference in your code.

 

Your current code has an extra END statement, thus won't run.

 

Also, since you're using the datepart function, it is assuming a datetime variable.

 

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 134

Re: exclude holidays from the count of days

I am able to execute the code. can you please advise where I have an extra end

Frequent Contributor
Posts: 134

Re: exclude holidays from the count of days

Here is a schedule showing dec 2016 and Jan 2017 holidays/weekend

 

Capture.PNG

 

 

here is the sample dataset that I am trying to use

 

 

Customer Name sch_dt Start_date End_date Workdays (w/o Holidays) Workdays (w/o Holidays) Notes
Customer 1 23DEC2016:14:13:03.840 23DEC2016:14:13:03.840 14JAN2017:10:15:00.000 15 13 Holidays: dec 23, Jan 2
Customer 2 29DEC2016:10:10:23.637 29DEC2016:10:10:23.637 04JAN2017:10:45:00.000 4 3 Holidays: jan 2
Customer 3 22DEC2016:10:58:00.000 22DEC2016:10:58:00.000 06JAN2017:09:45:00.000 11 8 Holidays: dec 23, dec 26, Jan 2
Customer 4 12JAN2017:11:07:50.910 12JAN2017:11:07:50.910 28JAN2017:08:00:00.000 11 10 Holidays: jan 16

 

 

 

column, Workdays (w/o Holidays), is what I am getting with the code shown. what I am not getting is the column values Workdays (with Holidays)

 

Thanks

 

 

 

Super User
Posts: 19,772

Re: exclude holidays from the count of days

 

Why don't you give it a shot and we can help you debug it?

I did post the method on your previous question. 

 

Super User
Posts: 19,772

Re: exclude holidays from the count of days

This does factor in weekends, it DOES NOT factor in if the holiday was on a weekend and days in lieu are provided by the company.

You haven't provided the rules for how that would be implemented, because I know it differs if the date was a Saturday vs Sunday sometimes.  

 

Super User
Posts: 19,772

Re: exclude holidays from the count of days

Also, you know we can't run your code if you include custom macro variables and reference data we don't have. A large part of your original issue was likely working with a datetime rather than date variable that could have been sorted out earlier if you'd provided accurate sample data.

Frequent Contributor
Posts: 134

Re: exclude holidays from the count of days

re. your previous suggestion (on my previous post), here is what I did that didn't worked out

 

----------------------------------------------------------------------------------------------------

Step A) created a permanent table using this code

 

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;

 

data dataset.wrkdays1;

set wrkdays;

run

 

 

 

now I have all the work days in table wrkdays

----------------------------------------------------------------------------------------------------

step B: then I ran following script to get holidays (2014 to 2020)

 

 

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 (2014, 2015, 2016, 2017, 2018, 2019, 2020) ;
by year ;
id year ;
run ;

 

 

----------------------------------------------------------------------------------------------------

step C: then I excluded holidays from the weekdays

 

Now my data has only workdays

----------------------------------------------------------------------------------------------------

Step D: ran following code

 

options intervalds=(workdays=dataset.wrkdays1);

  

data temp;
set db.v_data (where = (&date1 <= sch_dt < &date2 ));

workdays = intck('workdays',datepart(Start_Date), datepart(End_date));

 

 

SAS program ran for about 2 to 3 hours and then I killed it

 

 

I AM A LITTLE LOST HERE. CAN YOU PLEASE ADVISE WHAT TO TRY NEXT. Thanks for your assistance

Super User
Posts: 19,772

Re: exclude holidays from the count of days

@tparvaiz Regarding your code/approach - it's a correct method. However there was no code under Step C. Did you forget to copy and paste that into the post?

Respected Advisor
Posts: 4,173

Re: exclude holidays from the count of days

Hi @tparvaiz

 

Do you understand what the following statement in @art297 code does?

options intervalds=(workdays=wrkdays);

If you don't know then start by reading: http://www.sascommunity.org/wiki/Sometimes_One_Needs_an_Option_with_Unusual_Dates

 

What you basically need to do (and what Art's code does).

1. Create a SAS dataset which only contains dates which are working dates.

2. Use option INTERVALDS to define a custom interval using this working dates SAS dataset as source

3. Use intck() with the custom interval to calculate the number of working days between two dates

 

As for 3): intck() does logically noting else than counting the rows in your working days table between two dates.

 

If you've got already a data set with your company holidays then you could simply create a data set with all dates from Monday to Friday and also exclude all dates which are company holidays. I'll post a code sample for this one.

 

Using the holiday() function might also return the desired result but it will only work for the US - other countries have different dates i.e. for Labour Day, no Thanks Giving but other national holidays (i.e. Queens Birthday).

 

Code sample for creation of source table for custom date interval:

data company_holidays;
  input date :date9.;
  datalines;
26Dec2016
02Jan2017
16Jan2017
;
run;

data wrkdays (keep=begin);

  /* define hash lookup table with company holidays */
  if 0 then set company_holidays(keep=date);
  dcl hash h_ch(dataset:'company_holidays(keep=date))');
  h_ch.defineKey('date');
  h_ch.defineDone();

  /* populate table wrkdays with dates that are working days */
  format begin date9.;
  do begin='01Dec2016'd to '31Jan2017'd;
    /* exclude Saturday and Sunday */
    if weekday(begin) not in (1,7) then 
      do;
        /* exclude company holidays */
        if h_ch.check(key:begin) ne 0 then output;
      end;
  end;
run;

 

 

Solution
‎05-15-2017 01:37 PM
PROC Star
Posts: 7,468

Re: exclude holidays from the count of days

I was wrong! Your code runs. I got confused because the indentation was off.

 

Does the following do what you want?

data wrkdays (keep=begin);
  format begin date9.;
  array holidays(12);
  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;
      if weekday(date) eq 1 then do;
        i+1;
        date+1;
        holidays(i)=date;
      end;
      else if weekday(date) eq 7 then do;
        i+1;
        date+1;
        holidays(i)=date;
        i+1;
        date+1;
        holidays(i)=date;
      end;
    end;
    if not(date in holidays or weekday(date) in (1,7)) then do;
      begin=date;
      output;
    end;
  end;
run;

options intervalds=(workdays=wrkdays);

libname db '/folders/myfolders';  

data db.v_data;
  infile cards dlm='09'x dsd;
  informat sch_dt start_date End_date anydtdtm.;
  input Customer_Name $	sch_dt	Start_date	End_date;
  cards;
Customer 1	23DEC2016:14:13:03.840	23DEC2016:14:13:03.840	14JAN2017:10:15:00.000
Customer 2	29DEC2016:10:10:23.637	29DEC2016:10:10:23.637	04JAN2017:10:45:00.000
Customer 3	22DEC2016:10:58:00.000	22DEC2016:10:58:00.000	06JAN2017:09:45:00.000
Customer 4	01may2017:11:07:50.910	01may2017:11:07:50.910	20may2017:08:00:00.000
;

%let date1=01may2017:00:00:00;
%let date2=19may2017:23:59:59.999;
data temp;
  set db.v_data (where = ("&date1"dt <= sch_dt < "&date2"dt ));
  workdays = intck('workdays',datepart(Start_Date), datepart(End_date));
run;

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 134

Re: exclude holidays from the count of days

it worked... thanks for your assistance

☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 281 views
  • 2 likes
  • 4 in conversation