The Essential Guide to SAS Dates and Times, Second Edition (2014)

Chapter 5. Date and Time Functions

SAS has many functions to manipulate dates, times, and datetime values. The functions can be categorized according to what they do. You can obtain the current date, time, or datetime (as specified by the computer's clock). You can also easily extract pieces of dates, times, or datetimes as numerical values from their corresponding SAS values, or you can assemble SAS date, time, and datetime values from SAS variables or constants. Another set of functions operates with intervals such as weeks or months.

5.1 Current Date and Time Functions

Current date and time functions have no arguments and return SAS values as noted in the following table. The values are obtained from the operating system's clock.

Table 5.1: Current Date and Time Functions

5.2 Extracting Pieces from SAS Date, Time, and Datetime Values

The extraction functions all use a single argument (represented by arg in the following table), which represents a SAS date, time, or datetime value. This can be either a SAS variable name or the appropriate constant. If two-digit year values are used, the result will be subject to the YEARCUTOFF= option value in effect. They all return a numeric value as the result. The following tables are separated into functions that use dates as an argument, those that use datetimes as the argument, and those that use times as an argument. Each table gives examples of how to apply each function, along with relevant comments for each example.

Table 5.2: Functions Returning a Date Component and Requiring a SAS Date Value as an Argument

Table 5.3: Functions Returning a Datetime Component and Requiring a SAS Datetime Value as an Argument

Table 5.4: Functions Returning a Time Component and Requiring a SAS Time Value as an Argument

5.3 Creating Dates, Times, and Datetimes from Numbers or Other Information

5.3.1 Introduction

This series of functions will create SAS date, time, and datetime values from numerical variables or constants. While informats take complete date, time, and datetime references and translate them to their corresponding SAS value, these functions will create a SAS value from discrete pieces such as month, day, and year.

5.3.2 List of Functions and Their Descriptions

DATEJUL(Julian-date);

DATEJUL(Julian-date); creates a SAS date value from a numeric value representing a Julian date. Julian-date must be of the type yy(yy)ddd, where yy(yy) is two or four digits representing the year, and ddd must be a number from 1 to 365 (366 if a leap year). If you use two digits for the year, the YEARCUTOFF= option will be used to determine the century. The following table gives examples of how to apply this function:

DHMS(date,hour,minute,second);

DHMS(date,hour,minute,second); creates a SAS datetime value. All four arguments are required. date is a SAS date value, which can be either a numeric value or a date constant. If you use a two-digit year in a date constant, the date will be translated according to the YEARCUTOFF= option. hour, minute, and second are all numeric variables and/or constants. Hour, minute, and second are not restricted to their clock times. Therefore, hour can be greater than 24, while minute and second can be greater than 60. The following table gives examples of how to apply this function:

Example 5.1: Using DHMS() When You Already Have a SAS Date and Time

If you need to create a datetime from a SAS date and a SAS time value, you do not have to use the HOUR(), MINUTE(), and SECOND() functions to extract those components from the time value. Remember that SAS keeps track of time in seconds since midnight and that the secondsargument in the DHMS function can be greater than 59. Therefore, if you supply the SAS time value as the seconds argument and set hour and minute to zeros, that will work just fine. The following example demonstrates:

1 DATA ex5_1;

2 INPUT row $ sasdate :date9. sastime :time8.;

3 result = DHMS(sasdate,0,0,sastime);

4 fmt_result = result;

5 format sasdate date9. sastime timeampm.;

6 DATALINES;

7 A 16oct2015 17:30

8 B 08jun2016 11:00

9 C 14apr2015 00:00

10 ;;;

11 RUN;

12 PROC PRINT DATA=ex5_1 NOOBS LABEL SPLIT='\';

13 ID row;

14 VAR sasdate sastime result fmt_result;

15 FORMAT fmt_result datetime19. sasdate sastime;

16 LABEL row='Row'

17 result = '~{text_align=c}SAS Datetime Value\Calculated
Using\DHMS(sasdate,0,0,sastime)'

18 fmt_result = "Result Formatted with\DATETIME19. Format";

19 RUN;

The Result

The calculation of the datetime value is done in line 3 of the above program. The formats have been removed from the sasdate and sastime variables in the table for the purpose of illustrating the actual values that are being sent to the function. Row C above has been included to demonstrate that any valid SAS time value will work, even 0 seconds. What good is that? If you need to create a SAS datetime value and you only have a date without a time value, then you can use DHMS(sasdate,0,0,0) to convert your SAS date into a SAS datetime.

HMS(hour,minute,second);

HMS(hour,minute,second); creates a SAS time value. hour, minute, and second are all numeric variables and/or constants. None of the parameters are restricted to their clock times. Therefore, hour can be greater than 24, while minute and second can be greater than 60. All three arguments must be present or you will get a missing value as the result. The following table provides examples:

MDY(month,day,year);

MDY(month,day,year); creates a SAS date value from the arguments. All three arguments are required. month, day, and year are all numeric variables or constants. If year is two digits, the century will be determined by the YEARCUTOFF= option. If a value given for any of the arguments is not valid or missing, such as MDY(2,31,2014) (February 31, 2014), the function will return a missing value and give you an "invalid argument to function" message in the log. The following table shows examples:

NWKDOM(n, weekday, month, year);

NWKDOM(n, weekday, month, year); creates a SAS date value for a given weekday in a given week number from a given month and year. All of the arguments are numeric and can be represented by constants or numeric variables. If any of the arguments are missing, or not valid, then the function will return a missing value. n ranges from 1 (first) to 5 (last). Sometimes using the value of 5 will give the same result as 4, if the fourth week of the month is also the last week of the month. weekday ranges from 1 to 7, where 1 represents Sunday, and 7 represents Saturday.month can range from 1 (January) to 12 (December). year represents the year and is subject to the YEARCUTOFF= option if you use two digits for the year.

You can use this function to find dates that are expressed as "The first Saturday in May," or the "last Tuesday in August," easily. The following table provides examples of how this function works.

The bolded and italicized text above shows that the last week of the month and the fourth week of the month may or may not produce the same date.

YYQ(year,qtr);

YYQ(year,qtr); creates a SAS date value from the arguments. Both arguments are required. year is a numeric variable or constant representing the year, and qtr is a numeric variable or constant between 1 and 4, representing the quarter of the year. If year is two digits, the century will be determined by the YEARCUTOFF= system option. This function returns the date of the first day of the quarter in the given year. The following table provides examples of how this function works.

5.4 Calculating Elapsed Time, and the HOLIDAY() Function

Because SAS uses simple math as the basis for dates and times, you might think that calculating elapsed time or projecting into the future would be easy. It should be a matter of simple addition or subtraction. However, SAS provides several functions that deal with calculating elapsed time, and for the most part the function is going to be more accurate than simple math. For example, one of the mathematical equations for calculating age is (current date–date of birth)/365.25. This approximation uses the .25 to account for leap years, but it fails to take into account the exception for years that are divisible by 100 but not by 400. While you might rarely need such accuracy when calculating elapsed years, the efficiency of a SAS function might speed things perceptibly when working with big data.

5.4.1 Calculating Elapsed Time with DATDIF() and YRDIF()

These two functions were originally developed for use with securities calculations for specific financial instruments.

DATDIF(start,end,basis);

DATDIF(start,end,basis); calculates the number of days between two dates. start is the starting date, which can be a date constant, a numeric variable, or a SAS expression. end is the ending date, also a date constant, a numeric variable, or a SAS expression. basis is a character constant or variable that tells SAS how to calculate the difference. The start and end arguments are required, while basis is optional. basis has two possible values. Note that if you use a character constant for basis, remember that it will need to be enclosed in quotation marks, or you will get an error.

1. '30/360', which sets each month to 30 days, and the year to 360 days, regardless of how many days are in each month or year in the span between the two dates. If a day is at the end of a month (for example, February 28/29 or March 31), it will be considered as the 30th of the month.

2. 'ACT/ACT', which uses the actual number of days in each month and year in the span between the two dates. This is the default, and it is identical to subtracting start from end.

YRDIF(start,end,basis);

YRDIF(start,end,basis); calculates the number of years between two dates. It is almost always more accurate than using mathematical approximation, depending on the basis used and the desired result. start is the starting date, which can be a date constant, a numeric variable, or a SAS expression. end is the ending date, also a date constant, a numeric variable, or a SAS expression. basis is a character constant or variable that tells SAS how to calculate the difference. basis has five possible values, as compared with the two possibilities in the DATDIF() function:

1. '30/360', which sets each month to 30 days, and the year to 360 days, regardless of how many days are in each month or year in the span between the two dates. If a day is at the end of a month (for example, February 28/29 or March 31), it will be considered as the 30th of the month.

2. 'ACT/ACT', which uses the actual number of days in each month and year in the span between the two dates. This was the default basis through SAS version 9.2. You can use the alias "Actual," not 'ACT.'

3. 'ACT/360', which uses the actual number of days between the two dates to calculate the number of years, but it uses a 360-day year, regardless of how many days are in each year, so the result is number of days divided by 360.

4. 'ACT/365', which uses the actual number of days between the two dates to calculate the number of years, but uses a 365-day year, regardless of how many days are in each year, so the result is number of days divided by 365.

5. 'AGE', which is used to calculate a person's age. This is available starting with SAS version 9.3, and as of that release, 'AGE' is now the default.

As you can see, all four results are different, and this is due to the way they were calculated. Prior to version 9.3, this function was often used to calculate ages, but even the 'ACT/ACT' basis doesn't calculate ages precisely. The 'ACT/ACT' basis averages leap year days across the four years. Now let's examine the YRDIF function when the 'ACT/ACT' basis and the 'AGE' basis are used.

This looks as if the YRDIF() function will yield the same result for both the 'AGE' basis and the 'ACT/ACT' basis. Where is the difference? Let's look at another series of dates.

In groupings A and D, you see that the basis makes a difference, while in groupings B and C, the 'ACT/ACT' and 'AGE' return identical results. What makes groupings A and D so different? These are leap years, and the leap day is accounted for as a whole day for that given year, as opposed to the averaging of a quarter day per year performed by the 'ACT/ACT' algorithm.

(U.S. and Canada Only) HOLIDAY(holiday,year);

HOLIDAY(holiday,year); provides the date of selected holidays in any given year as a SAS date value. This function is valid for U.S. and Canada holidays only. holiday can be a character string enclosed in quotation marks or a character variable containing one of the arguments listed below. The valid values of holiday are listed in the table below. Note: If you use a character variable instead of a string, your variable should be at least 18 characters long to accommodate the longest argument.

5.5 The Basics of SAS Intervals

Some of the SAS functions described in section 5.4, such as DATDIF, are very good at calculating the exact amount of elapsed time between two SAS dates, and as demonstrated in some of the above examples, you can see the difference between the function and simple math. There aren't functions to project future dates, because it would seem simple enough: you just add a number of days, hours, or minutes, and you come up with an answer.

However, we frequently need to refer to units of time that are not uniform, such as months, which can be 28, 29, 30, or 31 days long. SAS provides functions to calculate intervals because, in many cases, simple math is still only an approximation. SAS has several standard interval definitions that are used with dates, times, and datetimes that represent many of the normal periods of time that we refer to, such as weeks or quarters. You are not restricted to the intervals given in the standard definitions, because you also have the ability to easily modify them. You can use multipliers and/or a shift index in conjunction with the standard intervals. Multipliers enable you to define intervals that are multiples of a standard interval and are not already defined, such as a decade or a century. A shift index enables you to define intervals that do not correspond with the starting values used by an interval (standard OR with a multiplier), such as a fiscal year that begins in July instead of January, or, to give you an example with a multiplier, a decade that starts in years ending in the number '5,' instead of years ending in 0. Section 5.6 will discuss the concepts of multipliers and the shift index in detail. If you need intervals that cannot be described by using multipliers and/or a shift index with the standard SAS intervals, SAS has the capacity for you to define your own intervals, and this is covered in depth in Section 5.7.

For the remainder of this book, when the term "interval" is used in a function definition, it means a SAS interval name, along with any multiplier and/or shift index unless explicitly specified otherwise. We will begin our discussion of intervals by providing a list of all the standard interval definitions and the periods that they describe in Table 5.5.

Table 5.5 SAS Interval Definitions Used with Dates, Times, and Datetimes

5.5.1 The Interval Calculation Functions: INTCK() and INTNX()

The interval calculation functions INTCK() and INTNX() use SAS interval definitions. INTCK() counts the number of intervals between two given dates, times, or datetimes. INTNX() calculates the date, time, or datetime that results after a given number of intervals have been added to an initial date, time, or datetime value.

The syntax for the INTCK function is as follows.

INTCK(interval, start-of-period,end-of-period,method);

interval is the SAS designation for a period of time, and can be a character literal or character variable that corresponds to one of the defined time intervals (see Table 5.5). start-of-period is the beginning date, time, or datetime value, while end-of-period is the ending one. Both start-of-period and end-of-period can be anything that evaluates to a valid SAS date, time, or datetime value.

As of SAS version 9, method determines how SAS is going to count the intervals. There are two possible values: CONTINUOUS (or C or CONT) and DISCRETE or (D or DISC). The default is DISCRETE, and this has been how intervals have traditionally been calculated in SAS. When method is DISCRETE, the INTCK() function is counting the number of times that the period interval begins between start-of-period and end-of-period, inclusive. It does not count the number of complete intervals between start-of-period and end-of-period. This also means that the count does not begin with start-of-period, but at the beginning of the first interval after that. The following example demonstrates how INTCK() counts using the DISCRETE method. For example, take the dates Saturday, December 31, 2011, and Sunday, January 1, 2012.

Example 5.2: How the INTCK() Function Counts by Default (method Is DISCRETE)

All of the intervals are equal to 1 even though only one day has passed! January 1, 2012, is the start of day, week, month, and year intervals. The starting day occurred on December 31, and the ending day began on January 1, so it is obvious that the result for the DAY interval should be 1, because the DAY interval boundary was crossed on January 1. However, when it comes to the WEEK interval, Sunday is the beginning of the week. Therefore, the week containing December 31st started on Sunday, December 25. Sunday, January 1, is the beginning of the next week, so you cross the WEEK interval boundary at January 1. This means that one WEEK interval has elapsed between the start of the week in December and the start of the week in January, so that causes the result for WEEK to be 1. Similarly, the month containing December 31 started on December 1, 2011, and the month for January 1, 2012, started on January 1. You are crossing the MONTH interval boundary on January 1, so one MONTH interval has elapsed between the start of the intervals for the two dates, and therefore, that result is 1 as well. Finally, the year for December 31, 2011, started on January 1 of 2011, while the year for January 1, 2012, starts on the same date. You cross the YEAR interval boundary at January 1, which causes the YEAR interval result to be 1. All of the values are equal to 1 because the INTCK() function is counting the DAY, WEEK, MONTH, and YEAR interval boundary, which occurs at Sunday, January 1, 2012, and not because of the number of days, weeks, months, or years that have passed.

Now let's look at the corresponding results when you use CONTINUOUS for the method.

Example 5.3: How the INTCK() Function Counts When method Is CONTINUOUS

One day has passed, as in the DISCRETE example. However, this looks more like what you might expect when you ask how many weeks, months, and years have passed between December 31, 2011, and January 1, 2012. Only one day has passed, not an entire week, month, or year. The CONTINUOUS method calculates continuous time, and it uses the calendar definition of a week, month, or year, starting on the date supplied as the first argument. Let's change the ending dates appropriately to reproduce the results in Example 5.2.

Example 5.4: How the INTCK() Function Counts When method Is CONTINUOUS

Now the picture should be a little more clear that using CONTINUOUS causes the INTCK() function to look at continuous elapsed time, not interval boundaries. To firm up this demonstration of the CONTINUOUS method for the INTCK() function, let's look at how the definition of the MONTH interval can change.

Example 5.5: How the INTCK() Function Counts When method Is CONTINUOUS

Rows A and B look normal: 31 days is a month, while 30 days is not a month. However, row C states that 30 days is a month. This is only true for the months of April, June, September, and November. Finally, rows D through F demonstrate that, when you are using the CONTINUOUS method, the MONTH interval changes based on the month and the year. In leap years, a period of 29 days across February is considered a month (row D). However, a period of 28 days is not considered a month in leap years (row E). On the other hand, if it is not a leap year, 28 days in February is a month.

The CONTINUOUS method is useful for calculating anniversaries and milestones tied to dates, times, and datetimes. Although this method seems much more intuitive than the traditional (and still default) way that SAS handles intervals, you will have to be very cautious in choosing which method you use and when. It is not recommended that you replace all of your existing code to use this method instead of the default, because the two methods differ in their definition of interval boundaries. The CONTINUOUS method shifts the interval by the starting date provided. With the DISCRETE method, all values within an interval boundary are considered to be equivalent. That makes it possible to group observations occurring within an interval for analysis.

Since the CONTINUOUS method is more intuitive to the way we tend to look at periods of time, the remainder of this section on the INTCK() function will be devoted to the traditional DISCRETE method. Please remember that the DISCRETE method is the default, and in order to use the CONTINUOUS method, you will have to supply the proper method argument in the INTCK() function. All of the following examples rely on the default method, so there is no method argument used in the function calls.

To complete the picture of how the traditional use of the INTCK() function works, let's look at the effect that the ending date has on INTCK().

Example 5.6: How the INTCK() Function Counts

Here you can see that although 6 days have elapsed, still only 1 week, month, and year have elapsed according to INTCK()! That is because the start of the week, month, and year interval for January 6, 2015, is still January 1, 2015, and that is what INTCK() is counting. Here are some more examples of the use of the INTCK() function with its default of the DISCRETE method.

Example 5.7: The INTCK Function: The Basics

Example 5.8: The INTCK Function: Counting Backward

In this example, the from date is after the to date, and therefore, the answer is negative. Since INTCK() counts interval boundaries, the answer is –5 because it starts counting at the start of a year. The start of the year interval for December 22, 2015, is January 1, 2015, so it is not counted. January 1, 2014; January 1, 2013; January 1, 2012; January 1, 2011; and January 1, 2010, are the boundaries of the YEAR intervals that it is counting, which corresponds to the beginning dates of each year.

Example 5.9: The INTCK Function: Counting Weekdays

If you are counting the number of work weekdays that have elapsed, you must be careful to remember that INTCK() counts interval boundaries and that the starting date is not counted in the answer. Row A above seems perfectly reasonable. You would expect that there would be two weekdays between Sunday, January 12, 2014, and Tuesday, January 14, 2014. Even though the starting boundary for WEEKDAY17W is Monday, the starting date is Sunday, January 12, 2014, so it counts Monday and Tuesday. However, you might think that there are two weekdays in the span Monday, January 13, 2014, to Tuesday, January 14, 2014. In row B, the INTCK() function is counting only 1 weekday, because it doesn't include Monday, January 13, 2014 (the start argument used in the function), when it counts the interval boundaries. Why is row C equal to zero then? First, Friday is the starting day, and the starting day is never included in the count. January 18, 2014, is a Saturday, and since we've defined the weekend days as Saturday and Sunday, those days are outside of any interval boundaries, so by definition, no interval boundaries have been passed.

Example 5.10 illustrates the difference between the three methods that have been discussed for calculating elapsed years using SAS. We will use the YRDIF() function using both the 'AGE' basis and 'ACT/ACT' basis. Since the INTCK() function also counts elapsed intervals, we will use it and show both the DISCRETE and CONTINUOUS methods, and compare those with mathematical estimation.

Example 5.10: The YRDIF() Function as Opposed to Mathematical Estimation and INTCK()

This uses the same dates, August 7, 1963, and May 8, 2014, for all five methods, but row A uses the YRDIF() function with 'ACT/ACT,' row B uses the same function, but with the 'AGE' basis. The two values are equivalent with the YRDIF() function, but as we have seen earlier, 'AGE' and 'ACT/ACT' only yield different results when the year being tested is a leap year. The mathematical approximation divides the number of days between the two dates by 365.25, and although the discrepancy is minute in this example, the difference is caused by the fact that the number of leap years in the period (11) is not evenly divisible by 4, rendering the value 365.25 an approximation. The INTCK function using the DISCRETE method counts interval boundaries from their beginning, so it is counting the number of January firsts between January 1, 1963, and January 1, 2015. In effect, unless you were born on January 1, using the DISCRETE method with INTCK() to calculate your age will make you old before your time! It is more appropriate in this type of scenario to use the CONTINUOUS method with the INTCK() function, which yields the answer 50 years old.

Those are some examples of the use of the INTCK() function. Remember, as of SAS version 9, the INTCK() function has an additional argument that you can use to change the way that INTCK counts interval boundaries. However, any existing code using INTCK() does not need to be changed, because the default method is the 'DISCRETE' method, which is how INTCK() has always worked. Once again, it is not a good idea to change existing code without considering how INTCK is being used, because the methods differ significantly in their calculations.

The next of the interval functions that we will consider is the INTNX() function. This function takes a given SAS date, time, or datetime value and calculates a new value by incrementing by the given number of intervals. Where INTCK() calculates the number of intervals between any two date, time, or datetime values, INTNX() takes the start of the period and increments it by a number of intervals to give the end of the period. The syntax of the INTNX() function is as follows:

INTNX(interval,start-from,number-of-increments,alignment);

INTNX(interval,start-from,number-of-increments,alignment); is one of the SAS intervals defined in Table 5.5 and can be a character literal or character variable that evaluates to one of the defined intervals. start-from is the starting date, time, or datetime value, which can be a constant, numeric variable, or a SAS expression. number-of-increments is an integer constant or a numeric variable that indicates how many intervals to advance. If it is not an integer, only the integer portion of the value will be used. alignment sets the returned date, time, or datetime value according to one of four predefined settings. The function calculates the dates at the beginning of the interval period, and then the alignment argument adjusts the result. The values are: Beginning or B, Middle or M, End or E, and Same or S (Sameday is also acceptable as an alias). The default value for alignment is Beginning. The Sameday argument cannot be used with the DTQTR, DTSEMIYEAR, or DTYEAR intervals, and it has no effect on time values.

The next series of examples demonstrates various uses and effects of the INTNX() function. The first example is the default use of the function with each of the date, time, and datetime intervals, while the second shows what happens when you use a non-integer as the increment to the function. Example 5.14 illustrates the use of the alignment arguments to yield specific dates.

Example 5.11: The INTNX() Function with Default Alignment

Each of the examples below increments the date, time, or datetime value by 3 of the interval shown in bold italics. For dates and datetimes, the start date is the same: Thursday, November 6, 2014. The only difference is that datetime intervals (interval names starting with DT) return datetime values, not dates. The interval values for datetime values are calculated in seconds, not days. By default, the INTNX() function returns the beginning of the interval given. If you want to change this, use the alignment argument discussed above.

Example 5.12: The INTNX() Function: Using Non-Integer Increments

When the number of increments argument is not an integer, SAS will take the integer part as the value. In this case, 2.5 becomes 2. The beginning of the first hour increment is 17:00, and the second is 18:00. Since alignment is the default value of B or beginning, that sets the answer to the beginning of the hour, which gives the result of 18:00.

Example 5.13: Counting Backward with the INTNX() Function

INTNX() always adds the number of intervals to the starting date, time, or datetime. If you want to find a prior date, then use a negative increment.

Example 5.14: The INTNX() Function with Alignment Arguments

Although the alignment arguments do change the value returned by the INTNX() function, it must be reiterated that the function performs the calculation based on the beginning of the interval, and the adjustment to the final result only occurs after that first calculation has been done.

5.6 Modifying SAS Intervals

The default SAS intervals don't match every situation. However, there are two ways to customize SAS intervals: You can modify the existing SAS intervals with multipliers and a shift index, or you can create your own intervals from a SAS data set. This section will discuss multipliers and the shift index. Section 5.7 will discuss creating your own intervals.

The interval function INTCK(interval,start-of-period,end-of-period,method) counts the number of intervals between two given SAS dates, times, or datetimes, while the INTNX(interval,start-from,number-of-increments,alignment) function advances a given date, time, or datetime by a specified number of intervals. Both functions use the standard SAS interval definitions. (See Table 5.6, below.) With the DISCRETE method, the INTCK() function counts at the starting point of a given interval, while the CONTINUOUS method adjusts the starting point of the interval based on the starting date and counts from there. Each method has its merits, and which one you use will depend on your analysis or reporting needs.

The INTNX() function always advances to the beginning of the given interval. The alignment argument available with INTNX() will adjust the result to the middle, end, or the same day of the interval. Nonetheless, the intervals are still measured from their starting point, and the adjustment is only applied after INTNX() has moved the specified number of intervals.

This behavior can lead to problems when your definition of an interval doesn't exactly match the standard SAS definition of that same interval. For example, the standard SAS definition of year has the first day of the year defined as starting January 1. What happens if your fiscal year starts on July 1, and that is how you want to measure your year? What if your semi-month period is truly 14 days long?

A shift index and interval multipliers enable you to do this. You have the ability to define the start of an interval definition by adding a shift index to it. The shift index defines the number of shift points to move the start of the interval. There is one restriction on the value of the shift index: It must be less than the number of shift points within the interval. For example, you can shift the start of a YEAR interval by up to 12 months, but not 13, as there are only 12 months in a year. This makes sense as if you were to shift a year by 13 months, you have shifted into the next YEAR interval, and this is much less intuitive than adding 1 year to the starting date(s) that you send to the interval function.

Table 5.6 shows the SAS interval name, definition, and the shift increment period for each one.

Table 5.6: SAS Intervals and Their Shift Points

For our first example, let's use the standard case of a fiscal year that starts on July 1. Technically, a shift index says that you are moving the start of the interval to the beginning of one of the subperiods within that interval. Years are subdivided into months, so that is the shift unit. The start point is always included in the count of subperiods to be shifted. The easiest way for me to remember this is that shifting an interval by one subperiod is the same as the unshifted base interval. Think of this another way: while the start of the year is January 1, the beginning of the first month in the year is also January 1. If we use a shift index of 1 with our YEAR interval, we are shifting to the beginning of the first month in the year.

Therefore, in order to move your YEAR interval by seven months, you need to move it to the beginning of the seventh subperiod. (January 1, February 1, March 1, April 1, May 1, June 1, July 1—that's seven). The shift value is added to the interval name by appending it with a decimal point.

Therefore, in order to advance the start of the YEAR interval by seven months to July 1, you would use the interval "YEAR.7," as illustrated by the following:

Example 5.15: Moving the Start of a Year Interval from January 1 to July 1

INTCK() counts the start of interval boundaries with the DISCRETE method (rows A and C). When the interval is "YEAR," it is measuring from January 1, 2013, until January 1, 2014 (the start of the year containing July 6, 2014). When the interval is "YEAR.7," you have shifted the beginning of the YEAR interval by 7 months, which declares that the year starts on July 1. In the example above, that shift causes the measurement to begin on July 1, 2012 (the start of the shifted "year" containing January 1, 2013) and end on July 1, 2014, which is the start of the year containing July 6, 2004. That is why the value returned in row C is 2, not 1. With the CONTINUOUS method, the shifted year starts on July 1, 2013, so the start date is before the start of the measurement. Therefore, the only interval boundary crossed is on July 1, 2014, so the answer in row D is 1.

While a shift index enables you to move the starting point of any given SAS interval, an interval multiplier enables you to define the length of your own intervals. You define your new interval by applying a multiplier value to an interval. For example, if you want to measure biweekly (14-day) periods, you would take the WEEK interval and multiply it by 2. You add the numeric multiplier to the end of the SAS interval name, so in this case your interval name would be "WEEK2." It measures 14-day periods starting on Sunday because the regular SAS interval WEEK starts on Sunday. Example 5.16 demonstrates the creation and use of a custom interval, by using the example of a multiplier of 2 for the WEEK interval. It also illustrates some of the features of custom intervals.

Example 5.16: Using an Interval Multiplier to Create a Custom Interval

In the above example, the start of the WEEK interval two weeks from February 9 is February 23, so it makes sense that the start of the second biweekly period from February 9 is March 9, because February 2014 has 28 days, which is the same as two of your WEEK2 intervals. This is not as simple as it seems. When using SAS intervals with the INTNX() function, you must keep in mind that intervals are always measured from the beginning of the starting interval to the beginning of the ending interval. This is independent of the starting and ending dates that you supply. Let's move the starting date in Example 5.16 backward by one week to February 2, 2014.

Advancing by two WEEKS (row 1) is still a difference of 14 days, as expected. What happened in row 2? Shouldn't you get 28 days instead of 21? No, because the start of the WEEK2 interval containing February 2, 2014, is January 24, 2014, and that is where the INTNX() function begins its count. Here is a sample program to produce the starting dates of the intervals:

Example 5.17: WEEK2 Intervals from January 1, 2014

DATA tricky;

DO intervals= 0 TO 5;

interval_start = INTNX('WEEK2','01jan2014'd,intervals);

OUTPUT;

END;

RUN;

PROC REPORT DATA=tricky NOWD SPLIT='\';

COLUMNS intervals interval_start;

DEFINE intervals / DISPLAY "Number of WEEK2\intervals from\January 1, 2014"
STYLE={CELLWIDTH=1.25in TEXT_ALIGN=C};

DEFINE interval_start / FORMAT=weekdate32. "Starting Date of Interval";

RUN;

The above program creates a table that shows the starting dates for the first five WEEK2 intervals of 2014. If the date(s) supplied fall between the starting dates of any two boundaries, the interval count (or incrementing) will begin from the starting date of the previous interval. This is why moving the starting date of the INTNX function by one week doesn't necessarily move the result by one week. You can move up to 14 days within a WEEK2 interval before you change the interval boundary.

How does SAS determine what the starting date of a given interval is if you use a multiplier? It takes the multiplied interval that you've created and starts counting beginning with January 1, 1960. This is true for all multiplied intervals except multiplied WEEK intervals. Multiplied WEEK intervals are counted starting from Sunday, December 27, 1959, because weeks are defined as starting on Sundays, and January 1, 1960, was a Friday.

You can also use a multiplier and a shift indicator together if needed. Interval multipliers are directly appended to the interval name (for example, WEEK2), and shift indicators are appended to the interval name with a leading decimal point (for example, WEEK.5). To use both the multiplier and a shift index, you first append the multiplier to the interval name to create a new interval name (for example, WEEK2), and then you append the shift indicator to the interval name. Given a multiplier of 2, and a shift of 5, the interval name becomes "WEEK2.5." Again, you cannot use a shift index that is greater than the number of shift points in the interval. In the case of a WEEK2 interval, you would not be able to use a shift index greater than 14, as that would place the starting point of your interval into the next WEEK2 interval. In that case, it would be more intuitive to change the starting date(s) that you will supply to the interval function.

To demonstrate, let's expand on the scenario used in example 5.17. What if you wanted your biweekly periods to start on January 1, 2014? January 1, 2014, was a Wednesday, so you want to move the starting date to the fourth day of the week (Sunday=1, therefore, Wednesday=4). Now we'll generate the same table as above, using a shift indicator of 4 days in addition to the biweekly interval of WEEK2.

Example 5.18: Using Both an Interval Multiplier and Shift Index to Create a Custom Interval

DATA tricky;

DO intervals= 0 TO 5;

plain_interval_start = INTNX('WEEK2','01jan2014'd,intervals);

shifted_interval_start = INTNX('WEEK2.4','01jan2014'd,intervals);

OUTPUT;

END;

RUN;

PROC REPORT DATA=tricky NOWD SPLIT='\';

COLUMNS intervals plain_interval_start shifted_interval_start;

DEFINE intervals / "Number of WEEK2\intervals from\January 1, 2014"

STYLE={CELLWIDTH=1.25in TEXT_ALIGN=C} DISPLAY;

DEFINE plain_interval_start /. "Starting Date of WEEK2 Interval"

FORMAT=weekdate32.;

DEFINE shifted_interval_start / "Starting Date of WEEK2.4 Interval"

FORMAT=weekdate32.;

RUN;

When you use a multiplier, you also have the ability to define your shifts within the entire interval created by the multiplier. For example, let's create a decade interval by using YEAR10 as the interval. Remember that intervals start at the beginning of the boundary, so the decades would start at the beginning of the first year of the decade. What can you do if you want to define the decade as starting in May of the middle year in the decade (for example, May of 1955 as opposed to January of 1950)?

To shift intervals across years, you need to use the first nested interval within the YEAR interval, which is MONTH. So you would use (number of years to shift*12) to calculate the number of months that you need to shift. If you wanted to shift 5 years, you would use 5*12=60. Add 5 to that, which shifts the starting month from January to May, and your interval definition is now YEAR10.65. That would be decades starting in May of years that end in 5. The code below shows the effect of moving the interval by 65 months.

Example 5.19: Shifting a Multiplied Interval

DATA tricky3;

DO intervals= 0 TO 7;

plain_interval_start = INTNX('YEAR10','01sep1950'd,intervals);

shifted_interval_start =
INTNX('YEAR10.65','01sep1950'd,intervals);

OUTPUT;

END;

RUN;

ods rtf file="c:\book\2ndEd\examples\ex5.6.5.rtf";

PROC REPORT DATA=tricky3 NOWD SPLIT='\';

COLUMNS intervals plain_interval_start shifted_interval_start;

DEFINE intervals / DISPLAY "Number of\intervals from\September 1, 1950"

STYLE={CELLWIDTH=1in TEXT_ALIGN=C};

DEFINE plain_interval_start / FORMAT=weekdate32.

"Starting Date of YEAR10 Interval";

DEFINE shifted_interval_start / FORMAT=weekdate32.

"Starting Date of Shifted YEAR10 Interval";

RUN;

The first thing to note is that even though the date that we specified is September 1, the starting date of the interval is January 1, because that is the start of the YEAR interval. In the second column, you can see that the interval has been shifted. Even though the starting date of the YEAR10. interval is January 1, 1950, the shifted interval itself starts on Tuesday, May 1, 1945 (bold italics added), not May 1, 1955. Why? Because it is the start of the interval that contains January 1, 1950.

The next example will demonstrate the importance of making sure that you use the correct shift index and that you have to be aware of the starting date and/or datetime for your shifted, multiplied interval.

Example 5.20: The Interaction between the Starting Date and the Interval Starting Point: The "Working Shift" Interval

A company has a 24-hour production cycle that consists of three 8-hour working shifts per day. It is easy to create an interval that represents each shift by using HOUR8 as your base interval, but shifts do not start at midnight, 8 a.m., and 4 p.m., which would be the default for the HOUR8 interval. The table below shows when the HOUR8 interval starts based on the default.

However, in this case, the actual shifts start at 6 a.m., 2 p.m., and 10 p.m., so we will need to use a shift index to align our HOUR8 interval with the actual shift hours. It is important to remember that when you are shifting intervals, a shift index of 1 moves the start of the interval to the start of the first subperiod within the interval—which is always the same as the start of the unshifted interval. Look at the following table, which will tell us what interval shift will give us the starting point at 6 a.m.

Notice that in the above table, we've used 6 a.m. as our reference time in the INTNX() function call, and we get the starting point of the interval by adding 0 intervals to our reference point. This method works for finding the starting point of any interval in relation to the reference point. Now we'll repeat our first table, showing the starting points for a 24-hour period starting at 6 a.m., with our HOUR8.7 interval to demonstrate that everything is correct.

Although it might seem odd that a shift index of 7 is needed to get a change of 6 hours, you must remember that the shift index is the number of subperiods within the interval to move. The shift is always to the beginning of each subperiod within the interval. Therefore, the first subperiod within an interval always starts at the beginning of the interval. In this case, the HOUR8 interval starts at midnight, and the first hour within the HOUR8 interval also starts at midnight. That is why the shift index necessary is 7, not 6.

In summary, the most important thing to remember about using intervals, multipliers, and the shift index is that all intervals, no matter how they are defined, are measured from their beginning. If you are using the INTCK() function, the definition of "beginning" is dependent upon the method that you use, DISCRETE or CONTINUOUS. With DISCRETE, it is considered to be the beginning of the interval boundary, but with CONTINUOUS, beginning is defined based on the start date provided. The INTNX() function measures the beginning from the interval boundary; if it is easier to think of INTNX as always using the DISCRETE method, then you can do that. However, remember that the alignment arguments "B", "M", "E", and "S" for the INTNX() function do not adjust the date until after the function has executed and calculated its start-of-interval result (also remember that the alignment arguments only work with the INTNX() function). No matter what, you can use the interval multipliers and a shift index to move the starting point of an interval, and you can use them anywhere that you can use a date, time, or datetime interval.

5.7 Creating Your Own SAS Intervals

Another way to create intervals for use with interval functions is by creating a SAS data set that defines the boundaries of your interval. This data set defines the duration of periods within the interval and the period across which the interval applies as well, which is an important concept that will be discussed when it comes to errors with using user-defined intervals. User-defined intervals enable you to create intervals that do not correspond with shifted and/or multiplied SAS intervals, such as intervals with irregular boundaries, those that do not encompass an entire predefined SAS interval, or intervals that are specific to your job, company, or industry. However, if you needed to count working shifts in a situation where there are two 10-hour work shifts per day with a four-hour break in-between while the machinery resets, you could not do it by using multipliers and a shift index.

You can also redefine an interval. For example, the shift point for a day is the day; what happens if you want your "day" to start at 6 a.m. instead of midnight? DTHOUR24.7 would create an interval that runs 24 hours starting at 6 a.m. (See example 5.20, which does the same for an HOUR8 interval. The only difference is that there are 24-hour-long subperiods in a DTHOUR24 interval.) Unfortunately, the original question was to shift a DAY interval by 6 hours, but you can't do that with a date value. DTHOUR intervals only apply to datetime values. You would have to convert all of your date data to datetime data in order to use that interval. Defining your own interval where days start at the desired time would solve this problem.

The SAS data set you have to create consists of at least one variable named begin. It can also have two other optional variables, end and season. If you do not include the end variable, it is assumed to be one less than the start of the next period within that interval; for example, if your user-defined interval is based on days, the end will be the day before the begin value for the next record. While seasonality is a time series concept outside of the scope of this book, it can be accounted for with the variable season. One additional requirement is that begin (and end, if present in the data set) must have an appropriate date, time, or datetime format assigned at the time that the data set is created. In order to associate this data set with a user-defined interval, you need to use the INTERVALDS system option.

OPTIONS INTERVALDS=(interval-name₁, dataset-with-interval-records_1, interval-name₂, dataset-with-interval-records₂…)

This is where you name your user-defined interval and tell SAS which data set to use for that interval. You can specify as many user-defined intervals as you need in a single OPTIONS INTERVALDS statement. interval-name must conform to standard SAS naming conventions, and it cannot be a SAS reserved word. If you try to use a reserved word, you will get an error at the OPTIONS statement. This applies to names of existing intervals defined by SAS as well. You cannot change the definition of an existing SAS interval by creating one of your own with the same name. dataset-with-interval-records is a standard SAS data set reference of the type libref.dataset-name. Since this statement merely identifies a data set for association with your interval name, data set options are not allowed.

For our first example, let's define a SEMESTER interval, based on an academic calendar with two semesters and a summer session. The sessions are defined as follows: The fall semester begins the day following the first Monday in September and ends on the third Friday in December. The spring semester begins on the second Monday in January, unless that day falls on the Martin Luther King, Jr., holiday. In that case, it begins on the day following the holiday. The summer session starts on the last Tuesday in May and proceeds through the third Friday in August. The following example uses both the NWKDOM() and HOLIDAY() functions described in sections 5.3 and 5.4, respectively.

Example 5.21: Academic Calendar: Creating a SEMESTER Interval

OPTIONS INTERVALDS=(semester=semester); ❶

DATA semester;

DO year=2014 TO 2017; ❷

/* Fall Semester */

begin = NWKDOM(5,2,8,year); /* Last Monday in August */

end = NWKDOM(3,6,12,year); /* Third Friday in December */

OUTPUT;

/* Spring Semester */

begin = NWKDOM(2,2,1,year+1); /* Second Monday in January of
following calendar year */

if begin eq HOLIDAY('MLK',year+1) then /* If MLK day, then Tuesday */

begin = begin + 1;

end = NWKDOM(2,6,5,year+1); /* Second Friday in May */

OUTPUT;

/* Summer Session */

begin = NWKDOM(1,2,6,year+1); /* First Monday in June */

end = NWKDOM(3,6,8,year+1); /* Third Friday in August */

OUTPUT;

END;

FORMAT begin end WEEKDATE.; /* Required */

RUN;

❶ tells SAS that we are defining an interval named "semester," and it is to be based on the data set WORK.SEMESTER.

❷ sets a definite limit on the interval, from the start of the 2014 academic year through the 2017 academic year, which ends in August 2018. Below is the resulting data set:

You can then use the SEMESTER interval that you have created in the INTCK or INTNX interval functions. The following table shows how many semesters have elapsed from September 15, 2014, to the respective end dates shown.

Example 5.22: Using a User-Defined Interval with INTCK

For a second example, let's consider a case where we need to count the actual number of working days for each year. The WEEKDAY interval is a useful approximation, but it doesn't account for holidays, so if you need to know the exact number of working days between two dates, this will solve that problem.

Example 5.23: Customized Company Working Days

OPTIONS INTERVALDS=(WorkingDays=Workdays);

DATA workdays (KEEP=begin); /* End variable not needed */

start = '01JAN2014'D;

stop = '31DEC2015'D;

nweekdays = INTCK('WEEKDAY',start,stop); /* Based on Mon-Fri weekdays */

DO i = 0 TO nweekdays;

begin = INTNX('WEEKDAY',start,i);

year = YEAR(begin);

/* Company-specific holidays */

/* Company closes day after thanksgiving */

xthanks = HOLIDAY("THANKSGIVING",year) + 1;

/* Christmas Eve */

xmaseve = HOLIDAY('CHRISTMAS',year) - 1;

/* Friday before Easter */

sprng = HOLIDAY("EASTER",year) - 2;

/* Founders Day Company Holiday. If on weekend, move forward or back */

founders = MDY(8,6,year);

SELECT(WEEKDAY(founders));

WHEN(6) founders = founders - 1;

WHEN(1) founders = founders + 1;

OTHERWISE founders = founders;

END;

/* Exclude dates of standard and company holidays from interval data set */

IF BEGIN ne HOLIDAY("NEWYEAR",year) AND

BEGIN ne HOLIDAY("MLK",year) AND

BEGIN ne HOLIDAY("USPRESIDENTS",year) AND

BEGIN ne HOLIDAY("MEMORIAL",year) AND

BEGIN ne HOLIDAY("USINDEPENDENCE",year) AND

BEGIN ne HOLIDAY("LABOR",year) AND

BEGIN ne HOLIDAY("VETERANS",year) AND

BEGIN ne HOLIDAY("THANKSGIVING",year) AND

BEGIN ne HOLIDAY("CHRISTMAS",year) AND

BEGIN ne xmaseve AND

BEGIN ne xthanks AND

BEGIN ne sprng AND

BEGIN ne founders THEN OUTPUT;

END;

FORMAT begin DATE9.;

RUN;

The following code generates a table showing the difference between the number of calendar days, the number of weekdays, and the number of working days for this company for the calendar year 2014.

DATA CountDays;

start = '01JAN2014'D;

stop = '01JAN2015'D;

ActualDays = INTCK('DAYS',start,stop);

Weekdays = INTCK('WEEKDAYS',start,stop);

ProductionDays = INTCK('WORKINGDAYS',start,stop);

LABEL ActualDays="Actual Days"

Weekdays="Weekdays"

ProductionDays = "Production Days"

start = "Starting Date"

end = "Ending Date"

;

FORMAT start stop DATE9.;

RUN;

PROC PRINT DATA=CountDays NOOBS;

RUN;

How much of a difference can this make? Consider that this company has six product lines and the time to manufacture each product varies. A customer comes in with a large order and wants to know what the delivery date would be for each product. It is critical that the delivery be guaranteed to the day, so the contract calls for a substantial discount if the delivery date is not met. In the following table, the order date is June 27, 2014. We use the INTNX() function to add the corresponding number of production days for each product to the order date. The approximate delivery date is calculated using the SAS WEEKDAY interval, while the true delivery date uses our user-defined WORKINGDAYS interval.

DATA production_lines;

LENGTH product $ 40;

orderDate = "27JUN2014"d;

Product = "Std Product 1";

days_from_order = 23;

OUTPUT;

Product = "Std Product 2";

days_from_order = 32;

OUTPUT;

Product = "Std Product 3";

days_from_order = 35;

OUTPUT;

Product = "Custom Product 1";

days_from_order = 33;

OUTPUT;

Product = "Custom Product 2";

days_from_order = 42;

OUTPUT;

Product = "Custom Product 3";

days_from_order = 56;

OUTPUT;

FORMAT orderdate weekdate.;

RUN;

DATA ordertime;

SET production_lines;

deliveryDate = INTNX('WORKINGDAYS',orderdate,days_from_order,'S');

approx_deliveryDate = INTNX('WEEKDAY',orderdate,days_from_order,'S');

FORMAT deliverydate approx_deliveryDate weekdate.;

RUN;

PROC REPORT DATA=ordertime NOWD SPLIT='\';

COLUMNS product days_from_order approx_deliveryDate deliveryDate;

DEFINE product / "Product";

DEFINE days_from_order / "Production Days\Required"

STYLE={TEXT_ALIGN=C};

DEFINE approx_deliveryDate / STYLE={TEXT_ALIGN=L}

"~S={text_align=c}Approximate Delivery Date\using WEEKDAYS Interval";

DEFINE deliveryDate / "~S={TEXT_ALIGN=C}Actual Delivery Date\using Custom\WORKINGDAYS Interval" STYLE={TEXT_ALIGN=L};

RUN;

There is one specific problem to be aware of when you are working with user-defined intervals. If a specific date, time, or datetime is not covered in the interval data set, and you try to use one of these values in a function, or one of these values is to be returned by a function, you will not get the answer that you expect. Such unexpected results can be avoided by ensuring that the dates that you define in the data set encompass all the dates that you may encounter. The next two examples will demonstrate what happens when you use a date that is out of the range of your user-defined interval. We will use the WORKINGDAYS interval as defined in the previous example. That interval is only defined for a period of 2 years, from January 1, 2014, through December 31, 2015. What happens if you try to calculate the number of working days using the INTCK() function with ending dates outside of that range, or you try to project a date using the INTNX() function that would be beyond that range?

Example 5.24: Out-of-Interval Calculation Using INTCK() and "WORKINGDAYS" Custom Interval

Rows 1 through 4 make sense. There are no working days from Thursday through Sunday, as December 24 is a company holiday. Rows 5 through 8 count a working day for Monday through Thursday as you would expect. Friday, January 1, 2016, is also a company holiday. Therefore, you would not expect it to count as a working day, nor would you expect the following Saturday and Sunday to count as workdays. But what about that first full week in January, rows 12 through 15? Those days aren't company holidays, so why aren't they being counted as working days?

The answer is that they aren't part of the WORKINGDAYS interval. The definition of the interval ended on December 31, 2015. Because they are not in the data set, any dates before or after the dates specified in the data set will not be considered working days. This makes sense when you consider how we initially created the company holidays. We did not include those dates in the data set that created the interval. Therefore, this problem actually starts with January 1, 2016. It is merely coincidental that January 1st is a holiday, and the 2nd and 3rd are weekend days; that is not what is preventing them from being counted as working days. Their absence from the data set we used to create the interval is the only reason that they are not being counted. This next example shows the type of problem that can arise when using the INTNX() function in a similar fashion.

Example 5.25: Out-of-Interval Calculation with INTNX() and "WORKINGDAYS" Custom Interval

What happened here? Rows 1 through 4 count the working days as expected, Monday through Thursday. However, if you try to increment the starting date by more than 4 working days, the function call returns a missing value. SAS cannot do the calculation because the result is undefined; it ran out of possible results to return at December 31, 2015, because you did not provide any in the data set.

The ability to define your own intervals will cover situations when the combination of standard SAS intervals, a multiplier, and a shift index does not suffice. It is not difficult to create the data set upon which SAS will base your user-defined interval, but be aware that dates that are not included in the interval can cause problems when you try to use the intervals that you create. If you see unexpected results from using your user-defined interval, the first thing to investigate is to make sure that the range that you have defined for your interval encompasses the range of possible results.

5.8 Interval Functions about Intervals

SAS has a series of functions that provide information about intervals that are being used. They can tell you which interval is the best fit between two or three dates, or suggest a format for a given interval. There is a function to tell you the shift point in effect for a given interval. (For basic intervals, this returns the values in Table 5.5, but interval multipliers can change the returned value.) There is also a function that will tell you if the interval name that you are using is valid. This can allow for increased automation and building robust applications.

5.8.1 INTFIT(argument-1,argument-2,type)

INTFIT(argument-1,argument-2,type) takes two date, datetime, or observation numbers as the first two arguments and returns the interval between them deemed to be "the best fit." This function assumes that the alignment is "SAME," so that the exact values are matched between the two arguments. type can be one of three case-insensitive values:

You can use this function to give you the interval between two points in time. The interval returned might contain a multiplier and/or a shift index. As you can see in the table below, some of the intervals are not as you might anticipate.

The italicized rows show examples of intervals that can be puzzling. Remember that the function provides the exact intervals between the pair of dates or datetimes, and the lowest common denominator will be used for base interval. In addition, the dates are measured using the sameday alignment, which means that the interval is measured from the beginning of the interval, and then adjusted. At first glance, the DAY5.5 result in row E doesn't seem to be right; Tuesday is only the third day of the week, so shouldn't the shift be 3 as it is for the DAY3 and DAY4 intervals? The start of DAY 5 intervals are Sunday, then Friday. If you want to measure a DAY5 interval to a Sunday from a Tuesday, then you will have to measure from the Friday, which gives you a shift of 5. Rows P and Q demonstrate that it might become even more confusing with times and/or datetimes, as the base interval shift points become minutes and/or seconds, so the resulting multipliers and shift indices might be large enough so that you lose the context of the reference point.

The following table shows a sample result of using the INTFIT() function with the 'OBS' type. The records are from a sequential data set with Wednesday, April 2, 2014, as the first date. Since the dates are keyed by observation, the result of the INTFIT() function shows the interval as the relationship between observations, not the actual date values themselves.

5.8.2 INTFMT('interval','size')

INTFMT('interval','size') takes the interval that you give and returns a suggested format for date, time, or datetime values using this interval. interval can be any standard SAS interval (with multipliers and/or a shift index if desired), or a user-defined interval that you have created, as shown in Section 5.6. If interval is represented by a character string, then it must be enclosed in quotation marks, but you can use a character variable containing the name of an interval instead. size refers to whether the format returned will have a two-digit year ('short'/'s') or a four-digit year ('long'/'l'). This argument must be enclosed in quotation marks also, but as with the interval argument, you can use a character variable for the argument. There is one important usage note about the size argument: If you do not use lowercase, the function might return an unpredictable result (see Row A) in the example table below:

This function is most useful when you use the INTFIT or INTGET functions to determine the interval dynamically, so that you will always display a result in the proper context. See example 5.23 to see how this can be used to dynamically format results.

5.8.3 INTGET(argument1,argument2,argument3)

INTGET(argument1,argument2,argument3) determines an interval from three date or datetime values that you provide. The arguments can be variables or date/datetime literals, but they must be of the same type; you cannot mix dates and datetimes as arguments. The function calculates all intervals between the first two arguments, and then between the second and third arguments. If the intervals are the same, it will return that interval. If the intervals are not the same, then the function will test the interval between the second and third arguments to see whether it is a multiple of the interval between the first two arguments. If this is true, then the function will return the smaller of the two intervals. If neither of these cases are true, then the INTGET() function will return a missing value.

In row A, the period between the dates is 15 days, which corresponds to the SEMIMONTH interval. Row B demonstrates that the dates do not have to have the same interval. The gap between January and April is one quarter, but the gap between April and October of the following year is five quarters. Since the interval for the second pair of arguments would be 'QTR5,' since it is a multiple of the QTR interval, the function returns the unmultiplied interval. What happened in row C? Simply, the interval between January 2014 and April 2015 is now QTR5, but the interval between April and October 2015 is QTR2. Since QTR2 is not a multiple of QTR5, the result is missing. Row D shows that if the interval between two arguments is itself a multiplied interval (MONTH2 versus MONTH6), the function will return the interval with the smallest multiplier, while row E shows the ingenuity of the function: The DAY17 interval works if it is shifted by 15 days.

5.8.4 INTSHIFT('interval')

INTSHIFT('interval') takes the interval that you give and returns the shift point for that interval. interval can be any standard SAS interval (with multipliers and/or a shift index if desired). If interval is represented by a character string, then it must be enclosed in quotation marks, but you can use a character variable containing the name of an interval instead. If you try to use this function with a user-defined interval that you have created, the function will return a missing value.

5.8.5 INTTEST('interval')

INTTEST('interval') takes the interval that you give and returns a 1 if it is a valid interval name, or 0 if it is not. You can use this to determine whether you have created a valid interval with multipliers and/or a shift index. interval can be any standard SAS interval (with multipliers and/or a shift index if desired). If interval is represented by a character string, then it must be enclosed in quotation marks, but you can use a character variable containing the name of an interval instead. If you try to use this function with a user-defined interval that you have created, the function will return a missing value.

5.9 Retail Calendar Intervals and Seasonality

This section covers intervals and functions that are most frequently used with time series analyses. Although the topic of time series analysis is beyond the scope of this book, these intervals and functions are described here because the intervals can be used with INTNX() and INTCK(), while the functions are a part of Base SAS. However, they are presented without context.

5.9.1 Retail Calendar Intervals

SAS has added intervals that are specifically designed for the retail industry. These intervals are ISO 8601 compliant, and can be used with any of the interval functions. They facilitate comparisons across years, because the weeks remain consistent between years. In order to facilitate this, some years will have leap weeks. Year definitions are based on the ISO 8601 definition of a week, which is the first Monday preceding January 4, which in some cases might place the beginning of the week in December. These intervals enable you to define the structure of your 52-week year, expecting that for the first 13-week period of your interval, there will be 1 month that has 5 weeks in it. This means that you can set the month pattern to 5-4-4, 4-5-4, or 4-4-5. You can work with years, months, or quarters in this fashion. The full list of retail intervals is given inTable 5.7.

Table 5.7: Retail Calendar Intervals

5.9.2 Seasonality Functions

Seasonality is used in time series analysis and can be used in SAS/ETS. It helps to account for normal seasonal variations in patterns inside an analysis. While it is not strictly a date and time matter, it does use intervals. Therefore, the seasonality functions are documented here, but without context. For more information about seasonality and its application, you can refer to the SAS/ETS documentation, and support.sas.com is a great place to find more help on this topic.

INTCINDEX('interval',argument)

INTCINDEX('interval',argument) returns the index of the seasonal cycle based on interval for argument, where argument is a SAS date, time, or datetime value. interval can be any standard SAS interval (with multipliers and/or a shift index if desired).

INTCYCLE('interval',seasonality)

INTCYCLE('interval',seasonality) returns the interval of the seasonal cycle. interval can be any standard SAS interval (with multipliers and/or a shift index if desired). seasonality is an optional argument that enables you to define seasonal cycles, and it can be a number or a cycle (such as 'YEAR'). For example, you can use the seasonality argument to specify your year as having 53 weeks instead of 52.

INTINDEX('interval',argument,seasonality)

INTINDEX('interval',argument,seasonality) returns the seasonal index when given an interval, a SAS date, time, or datetime of the seasonal cycle. interval can be any standard SAS interval (with multipliers and/or a shift index if desired. argument is a SAS date, time, or datetime value. Note that the interval specified must be appropriate for the argument. seasonality is an optional argument that enables you to define seasonal cycles, and it can be a number or a cycle (such as 'YEAR'). The INTINDEX function returns the seasonal index, while the INTCINDEX function returns the cycle index.

INTSEAS('interval',seasonality)

INTSEAS('interval',seasonality) returns the number of intervals in a seasonal cycle. interval can be any standard SAS interval (with multipliers and/or a shift index if desired). seasonality is an optional argument that enables you to define seasonal cycles. This is a good function to be aware of if you are not familiar with concepts of seasonality, because the number of intervals in a seasonal cycle might not be intuitive. For example, while you might expect the number of intervals for the QTR interval to be 4 (because there are 4 quarters in a year), the number of intervals for a DAY interval is 7, for the number of days in the week. This can help you check your expectations.