Blogs

Blogs

Shifting a date by a given number of workdays

20
By Topics | Learn SAS Programming Tips

Workday calendar including weekends and holidays

Way too often, SAS programmers run into a task when for a given date (e.g. event date) there is a need to shift (add or subtract) it by a specified number of days excluding weekends and holidays — in other words to move a date by a given number of workdays. It does not matter how many days off are in our date span as long as it contains exactly the required number of workdays.

For the purpose of this blog post, we will use the following words as synonyms: workdays, work days, working days, business days; as opposed to their antonym: days off.

In the ideal world

If not for gifts from governments and employers called holidays, shifting (incrementing or decrementing) a date by a number of workdays using SAS would be a piece of cake. It’s literally a single line of code using INTNX function with the WEEKDAY date interval. For example, the following DATA Step code:

data _null_;
   event_date = '02JAN2020'd;
   shift_date = intnx('weekday', event_date, -10);
   put shift_date= date9.;
run;

produces in the SAS log:

shift_date=19DEC2019

Here:

  • 'weekday' is date interval covering Monday through Friday;
  • event_date is starting date point;
  • -10 is number of workdays to shift by (positive number means increment; negative number means decrement).

Note, that the WEEKDAY date interval can be modified to accommodate different weekend days. For example:

  • WEEKDAY17W - five-day work week with a Sunday (1) and Saturday (7) weekend (same as WEEKDAY);
  • WEEKDAY1W - six-day week with Sunday (1) as the only weekend day;
  • WEEKDAY67W - five-day week with Friday (6) and Saturday (7) as weekend days, etc.

Holidays schedule

In the real world, however, weekends defined by the WEEKDAY interval are not the only days off, as they do not account for holidays. In the example above, when we shifted our starting date (2 January 2020) by -10 we arrived at 19 December 2019 which means we miscounted several holidays as workdays.

Which holidays (and how many) we miscounted depends on jurisdiction (country, state, business), as their holidays schedules vary. For example, for US federal agencies we would miss (1. New Year – 1Jan2020, 2. Christmas Day – 25Dec2019, and 3. Christmas Eve Day – 24Dec2019 – although this is not an official US federal holiday, most federal employees were given that day off by presidential executive order).

For SAS Institute (USA), we would miscue 6 non-weekend holiday days (Winter Holiday 25Dec2019 – 27Dec2019 and 30Dec2019 - 1Jan2020).

In other countries or businesses, this holidays schedule might be quite different, and that is why this date-shifting task that would account for holidays schedule causes so much confusion. Let’s straighten it out with the help of our old friend – SAS user-defined format. But first, let’s create a workday calendar – a data table listing all OUR work days as well as days off.

Workday calendar

Practically every organization has (or must have) a workday calendar that defines the valid working days and consists of a repeating pattern of days on and days off, as well as exceptions to that pattern. While such a calendar may span multiple years, for our purposes, we can use a subset of that calendar, which reliably covers the date range of our interest.

Let’s create an example of the workday calendar as a SAS data table:

data DAYS_OF_WEEK;
   format DATE date9.;
   do DATE='01JAN2019'd to '31JAN2020'd;
      WEEK_DAY = weekday(DATE);
      DAY_NAME = put(DATE,downame.);
      WORK_DAY = 1<WEEK_DAY<7;
      output;
   end;
run;
 
data DAYS_HOLIDAY;
   format DATE date9.;
   input DATE date9.;
   WORK_DAY = 0;
   datalines;
01JAN2019
21JAN2019
18FEB2019
27MAY2019
04JUL2019
02SEP2019
11NOV2019
28NOV2019
24DEC2019
25DEC2019
01JAN2020
20JAN2020
; 
 
/* Overlay holidays onto weekdays */
data DAYS_WEEKENDS_AND_HOLIDAYS;
   merge
      DAYS_OF_WEEK
      DAYS_HOLIDAY;
   by DATE;
run;

Here is a fragment of the resulting workday calendar table:
Workday calendar table

If date shifting is needed on an individual-level, then workday calendars should be created for every person and must include working days, weekends, holidays as well as personal days off such as vacations, sick days etc.

SAS format to distinguish workdays from days off

Now, for the dates range of our interest, we want to create a SAS user-defined format that lists all the days off while workdays are lumped into the other category. It’s just more efficient that way, as the number of days off is usually smaller than the number of work days so our explicit list of dates will be shorter. For example:

proc format;
   value dayoff
   '01DEC2019'd = 'Y'
   '07DEC2019'd = 'Y'
   '08DEC2019'd = 'Y'
   . . .
   '24DEC2019'd = 'Y'
   '25DeC2019'd = 'Y'
   '01JAN2020'd = 'Y'
   '20JAN2020'd = 'Y'
   other = 'N'
   ;
run;

In this user-defined SAS format values labeled 'Y' mean day off, and values labeled 'N' mean workday. That includes and takes care of both weekends and holidays.

The proc format above serves only for illustrational purposes of what kind of format we are going to create. However, by no means do I suggest implementing it this hard-coded way. Quite the contrary, we are going to create format dynamically and 100% data-driven. Here is how we do it:

data WORK.DAYSOFF (rename=(DATE=START));
   set DAYS_WEEKENDS_AND_HOLIDAYS(where=(WORK_DAY=0)) end=last;
   retain FMTNAME 'dayoff' TYPE 'n' LABEL 'Y';
   output;
   if last then do;
      HLO = 'O';
      LABEL = 'N';
      output;
   end;
run;
 
proc format cntlin=WORK.DAYSOFF;
run;

In the above code, HLO='O' and LABEL='N' are responsible for generating the OTHER category for the dayoff format.

Shifting dates by a number of workdays using dayoff format

With the dayoff user-defined format at hands, we can easily increment or decrement dates by a number of workdays. Here is how:

/* data table of some dates */
data EVENTS;
   do EVENT_DATE='01DEC2019'd to '31DEC2019'd;
      output;
   end;
   format EVENT_DATE date9.;
run;
 
/* Calculating new dates shifted by a number of workdays */
data EVENTS_WITH_SHIFTS;
   set EVENTS;
 
   /* Decrement EVENT_DATE by 10 workdays */ 
   d = EVENT_DATE;
   do i=1 to 10;
      d = d - 1;
      if put(d, dayoff.)='Y' then i = i - 1;
   end;
   BEFORE_DATE = d;
 
   /* Increment EVENT_DATE by 12 workdays */ 
   d = EVENT_DATE;
   do i=1 to 12;
      d = d + 1;
      if put(d, dayoff.)='Y' then i = i - 1;
   end;
   AFTER_DATE = d;
 
   format BEFORE_DATE AFTER_DATE date9.;
   drop d i;
run;

In this code, we decrement (d=d-1) or increment (d=d+1) our event date every time the do-loop iterates. It will iterate while counter i does not exceed the number of workdays. However, within the do-loop we modify counter i to i-1 every time we come across a day off as determined by condition put(d,dayoff.)='Y'. This will effectively exclude days off from counting towards the number of workdays. The do-loop will iterate the number of workdays plus the number of days off thus moving date d by the number of days that includes exactly the given number of workdays (plus some number of days off which we don’t care about). Just pause for a second and think to absorb this.

This simple technique can be modularized by implementing it as a SAS user-defined function or a SAS data-step macro.

User-defined function to shift a date by a number of workdays

Here is the user-defined function shiftwd() that shifts a beginning date specified in the first argument from_date by a number of workdays specified in the second argument shift_by. The second argument can be either positive or negative. Positive second argument means advancing the first argument (incrementing); negative second argument means subtracting workdays from the first argument (decrementing). Both arguments can be either variable names or numerals representing whole numbers.

libname funclib 'c:\projects\shift\functions';
proc fcmp outlib=funclib.funcs.dates; 
   function shiftwd(from_date, shift_by); 
      d = from_date; 
      do i=1 to abs(shift_by); 
         d = d + sign(shift_by); 
         if put(d,dayoff.)='Y' then i = i - 1; 
      end; 
      return(d); 
   endfunc; 
run;

Function usage example:

libname funclib 'c:\projects\shift\functions';
options cmplib= funclib.funcs;
 
data EVENTS_WITH_SHIFTS;
   set EVENTS;
   BEFORE_DATE = shiftwd(EVENT_DATE,-10); /* Decrement EVENT_DATE by 10 workdays */ 
   AFTER_DATE  = shiftwd(EVENT_DATE, 12); /* Increment EVENT_DATE by 12 workdays */ 
   format BEFORE_DATE AFTER_DATE date9.;
run;

SAS macro to shift a date by a number of workdays

Similarly, the same can be implemented as a data-step macro:

%macro shiftwd (fromvar=,endvar=,wdays=,sign=);
   &endvar = &fromvar;
   do i=1 to &wdays;
      &endvar = &endvar &sign 1;
      if put(&endvar, dayoff.)='Y' then i = i - 1;  
   end;
   drop i;
%mend;

This macro has 4 required parameters:

  • fromvar - variable name of the beginning date;
  • endvar - variable name of the ending date;
  • wdays - variable name or numeral representing number of workdays to move from the beginning date;
  • sign - operation sign defining direction of the date move (+ for incrementing, - for decrementing).

Macro usage example:

data EVENTS_WITH_SHIFTS;
   set EVENTS;
   %shiftwd(fromvar=EVENT_DATE,endvar=BEFORE_DATE,wdays=10,sign=-); /* Decrement EVENT_DATE by 10 workdays */ 
   %shiftwd(fromvar=EVENT_DATE,endvar=AFTER_DATE, wdays=12,sign=+); /* Increment EVENT_DATE by 12 workdays */ 
   format BEFORE_DATE AFTER_DATE date9.;
run;

Related materials

Calculating the number of working days between two dates (Blog post)

Custom Time Intervals (SAS Documentation)

Your thoughts?

Do you find this material useful? How do you handle the task of adding or subtracting workdays from a date? Please share in the comments section below.

Tags SAS Programmers sas programming tips & techniques
Share

About Author

Leonid Batkhan

Leonid Batkhan is a long-time SAS consultant and blogger. Currently, he is a Lead Applications Developer at F.N.B. Corporation. He holds a Ph.D. in Computer Science and Automatic Control Systems and has been a SAS user for more than 25 years. From 1995 to 2021 he worked as a Data Management and Business Intelligence consultant at SAS Institute. During his career, Leonid has successfully implemented dozens of SAS applications and projects in various industries. All posts by Leonid Batkhan >>>

20 Comments

  1. JC on

    Even Excel has a function to do this without too much trouble, with all cost invested on the software no one realized this was basic?

    Reply
    • Leonid Batkhan
      Leonid Batkhan on

      I wouldn't cite Excel as a perfect solution for shifting a date by a number of workdays including holidays. MS Excel offers WORKDAY function and WORKDAY.INTL function both of which allow for defining custom holidays. However, these holidays must be specified / listed in the function call itself which makes it a hard-coded solution, - not the best programming practice, especially when dealing with a wide range of dates. The goal of this blog post is to demonstrate a universal data-driven approach for solving this problem without having to explicitly supply hard-coded holidays list in the function call.

      Reply
  2. Jonas V. Bilenas on

    Leonid,

    Very useful information. I presented similar concepts of INTNX a my last PROC FORMAT presentation, "Using User Defined FORMATS and the INTNX Date Function to Extract LAGS and LEADS" at the Philadelphia SAS Users Group (PhilaSUG) Spring 2019 meeting. I got to learn more about PROC FCMP that you mentioned in this useful post.

    My presentation can be found at these 2 links:
    https://jonasbilenascom.wpcomstaging.com/wp-content/uploads/2019/06/Format_Leads_Lags.pdf
    http://philasug.org/Presentations/201906/Jonas_Format_Leads_Lags.pdf

    Thanks,

    Jonas V. Bilenas

    Reply
      • Jonas V. Bilenas on

        Leonid,

        Thank you for your blog!

        I forgot to include my PharmaSUG 2019 paper in my last reply to your blog. The paper included INTNX and FORMAT CATALOGS where I added leads and lags applications..

        https://www.pharmasug.org/proceedings/2019/BP/PharmaSUG-2019-BP-057.pdf

        That was my first PharmaSUG I attended in 2019. I had more free time in 2019 and enjoyed the conference very much. You always learn new methodologies in SAS Regional and SAS Global Forum conferences and get to meet up with fellow SAS users. You get to learn new insights at the conferences and local user groups even if some of the presentations are not slated to your industry experience. At the PharmaSUG 2019 conference I met up with Philip Holland. I don't recall ever meeting with him in person. We were both working the Code Doctor Sessions at the same time. What a great memory.

        Jonas

        Reply
    • Leonid Batkhan
      Leonid Batkhan on

      Thank you, Derek, for your share. Very useful paper. Great emphasis in your code example: "MUST associate begin and end with a date, time or datetime format as appropriate, otherwise user-defined interval will not work".

      Here is the problem that I was and still mystified with. Custom intervals work fine when advancing (incrementing) date. However, when going back (decrementing) date, they produce incorrect results for start dates falling on a weekend or holiday; the results are off by one day earlier than they need to be.

      I ran the following code to compare the same events, calendar, and shifts with the example provided in the blog post:

      /* USING CUSTOM INTERVALS */
data WORK.WORKDAYS (rename=(DATE=BEGIN));
   set DAYS_WEEKENDS_AND_HOLIDAYS(where=(WORK_DAY=1));
   format DATE date9.;
run;

options INTERVALDS=(workdays=WORK.WORKDAYS);

data EVENTS_WITH_SHIFTS1;
   set EVENTS;
   BEFORE_DATE = intnx('workdays',EVENT_DATE,-10); /* Decrement EVENT_DATE by 10 workdays - INCORRECT RESULTS*/ 
   AFTER_DATE  = intnx('workdays',EVENT_DATE, 12); /* Increment EVENT_DATE by 12 workdays */ 
   format BEFORE_DATE AFTER_DATE date9.;
run;

proc compare base=EVENTS_WITH_SHIFTS compare=EVENTS_WITH_SHIFTS1;
run;

      In order to produce correct results matching the example in this blog post, I had to modify the INTNX() function arguments to be
      BEFORE_DATE = intnx('workdays',EVENT_DATE-1,-(10-1)), see below:

      data EVENTS_WITH_SHIFTS1;
   set EVENTS;
   BEFORE_DATE = intnx('workdays',EVENT_DATE-1,-(10-1)); /* Decrement EVENT_DATE by 10 workdays - CORRECT RESULTS*/ 
   AFTER_DATE  = intnx('workdays',EVENT_DATE, 12);       /* Increment EVENT_DATE by 12 workdays */ 
   format BEFORE_DATE AFTER_DATE date9.;
run;

proc compare base=EVENTS_WITH_SHIFTS compare=EVENTS_WITH_SHIFTS1;
run;

      I couldn't explain such a behavior and that is why I resorted to a more intuitive (at least to me) way of solving this problem with user-defined format.

      Reply
    • Leonid Batkhan
      Leonid Batkhan on

      Fareeza, it's a great question. That was my initial intent to create a custom time interval and then use intnx('custom_interval',...) function as you suggested, but ... I was not successful in that direction. Then I came up with the solution described in this post and, since it was exactly what I needed, I shared it with our readers. I did provide reference to the Custom Time Intervals documentation in the Related materials section at the end of this post in hopes it will inspire and challenge our readers. You are welcome to share with us your solution using custom intervals. It will be greatly appreciated, especially if you provide an example matching the one described in this post.

      Reply
  5. Michael Raithel on

    Leonid,

    This is another "solid gold" blog post that both experienced and neophyte SAS programming professionals can learn from! The clever techniques that you lay out will never be outdated. Thank you!

    Reply
  6. Susan Slaughter on

    Leonid,
    Thanks for another eminently useful blog post! Dates are one of those things that everyone thinks they understand, but that turn out to be devilishly complex in practice. (This brings back memories of when I worked for a television ratings company. Yikes!) Next time I am faced with one of these problems, I'll refer back to your post.
    best,
    Susan

    Reply

Leave A Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top