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:
- '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.
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.
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:
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;
Calculating the number of working days between two dates (Blog post)
Custom Time Intervals (SAS Documentation)
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.
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?
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.
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:
Jonas V. Bilenas
Thank you, Jonas, for sharing your complementary information. Great addition to this blog post.
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..
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.
I have a paper that explains how to do exactly that. It's called "Demystifying Intervals", and tries to work through the common misperceptions of SAS intervals, which are pretty powerful once you get the hang of it. The custom interval scenario is on pages 6 and 7, the code that creates the custom interval in the example is on pages 13 and 14.
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:
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:
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.
Any reason why you wouldn't create a custom calendar and use INTNX()?
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.
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!
Thank you, Michael, for such nice and encouraging words.
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.
Thank you, Susan, for your comment. Agree, dates (and times) are tricky ones. My favorite is related to the daylight saving time (DST): In the USA, Sunday, March 8, 2020, 2:00:00 am clocks are turned forward 1 hour to Sunday, March 8, 2020, 3:00:00 am local daylight time. So the timeline on March 8, 2020 is ... 1:59:58 am, 1:59:59 am, 3:00:00 am, 3:00:01 am and so on. On top of it, states of Arizona and Hawaii do not observe DST...
_Most_ of Arizona. And it's an even bigger mess if you go back in time: https://en.wikipedia.org/wiki/Daylight_saving_time_in_the_United_States
You are absolutely right, Gordon. On Sunday, November 1, 2020, clocks are turned back from 2:00 am to 1:00 am so the time span from 1:00 am to 2:00 am will be repeated twice. Add to this time zones (there are 6 of them in the USA - Hawaii, Alaska, Pacific, Mountain, Central, and Eastern) and you get a total mess, and a good reason to like Greenwich Mean Time (GMT), to which Daylight Saving Time never applies.
While it is true that GMT can be constant all year, but we also have Daylight Saving Time in the UK (called British Summer Time) from 2am on the last Sunday in March to 2am on the last Sunday in October. Europe used to end Daylight Saving Time on the last Sunday in September until 1996, when it changed to the same date as in the UK. Finally, from October 2022 Europe will no longer use Daylight Saving Time.
Who said dates and times were boring!..............Phil
Well said, Philip!
This is why SAS beefed up their ISO 8601 capabilities...
Note to our readers:
The International Organization for Standardization (ISO) developed the ISO 8601 standard for representing dates and times, that is also adopted by Clinical Data Interchange Standards Consortium (CDISC) and widely used in clinical trials. Here is a couple of useful resources for ISO 8601:
- Working with Dates and Times by Using the ISO 8601 Basic and Extended Notations
- Harnessing the Power of SAS ISO 8601 Informats, Formats, and the CALL IS8601_CONVERT Routine
Before Y2K (2000), in the consumer banking industry, I was working with Full File Credit Bureau transaction files (multiple records per applicant or customer). I recall that one of the bureaus was using 3 digits to reflect an account open date and possibly for charge off dates. If I recall, at that time, bureaus would only keep transaction records for per applicant for up to 7 years. You had to read the date as a character variable to get the correct DATE information along with interpreting special codes for over 5 years and over 10 years. Not sure if it is done that way after Y2K.