The standard time intervals described in the previous sections do not always fit the data. For example, you might want to use fiscal months that begin on the 10th of each month, but the MONTH interval begins on the 1st of each month. Or you might collect data hourly for a business that is closed at night, but using the DTHOUR interval results in gaps in the data that can cause problems in standard time series analysis. In another case, you might wish to calculate the number of business days between dates, excluding holidays and weekends, but holidays are counted when you use the INTCK function with the WEEKDAY interval. For more information about the INTCK function, see Interval Functions INTNX and INTCK.
Time series can be analyzed using observation numbers as the identifying reference. However, it is often desirable to maintain the time stamp for other types of modeling such as regression variables based on time or reconciliation.
To address these issues, you can define custom intervals within a given SAS program. The use of custom intervals requires the following two steps for each interval:
Associate a data set name with a custom interval name by using the INTERVALDS= system option. For more information about the
INTERVALDS= option, see the
SAS Language Reference: Dictionary. The following example associates the data set StoreHoursDS
with the custom interval StoreHours.
options intervalds=(StoreHours=StoreHoursDS);
Create a data set that describes the custom interval. The data set must contain a BEGIN
variable. It can also contain an END
and a SEASON
variable. It should contain a FORMAT statement for the BEGIN
variable that specifies a SAS date, SAS datetime, or numeric format that matches the BEGIN
variable data. If the END
variable is present, it should also be included in the FORMAT statement. A numeric format that is not a SAS date or SAS datetime
format indicates that the values are observation numbers. If the END
variable is not present, then the implied value of END
at each observation is one less than the value of BEGIN
at the next observation.
The span of the custom interval data set should include any dates or times that are necessary for performing calculations on the time series, including backcasting, forecasting, and other operations that might extend beyond the series (such as filters).
After the two preceding steps have been completed, the custom interval can be specified in SAS procedures and functions where a standard time interval can be specified.
The following DATA step creates the StoreHoursDS
data set, which is appropriate for a business that is open 9AM to 6PM Monday through Friday and Saturday 9AM to 1PM:
options intervalds=(StoreHours=StoreHoursDS); data StoreHoursDS(keep=BEGIN END); start = '01JAN2009'D; stop = '31DEC2009'D; do date = start to stop; dow = WEEKDAY(date); datetime=dhms(date,0,0,0); if dow not in (1,7) then do hour = 9 to 17; begin=intnx('hour',datetime,hour,'b'); end=intnx('hour',datetime,hour,'e'); output; end; else if dow = 7 then do hour = 9 to 12; begin=intnx('hour',datetime,hour,'b'); end=intnx('hour',datetime,hour,'e'); output; end; end; format BEGIN END DATETIME.; run;
title 'Store Hours Custom Interval'; proc print data=StoreHoursDS(obs=18); run;
The first 18 observations of the custom interval data set are shown in Figure 4.1.
Figure 4.1: Store Hours Custom Interval
Store Hours Custom Interval |
Obs | begin | end |
---|---|---|
1 | 01JAN09:09:00:00 | 01JAN09:09:59:59 |
2 | 01JAN09:10:00:00 | 01JAN09:10:59:59 |
3 | 01JAN09:11:00:00 | 01JAN09:11:59:59 |
4 | 01JAN09:12:00:00 | 01JAN09:12:59:59 |
5 | 01JAN09:13:00:00 | 01JAN09:13:59:59 |
6 | 01JAN09:14:00:00 | 01JAN09:14:59:59 |
7 | 01JAN09:15:00:00 | 01JAN09:15:59:59 |
8 | 01JAN09:16:00:00 | 01JAN09:16:59:59 |
9 | 01JAN09:17:00:00 | 01JAN09:17:59:59 |
10 | 02JAN09:09:00:00 | 02JAN09:09:59:59 |
11 | 02JAN09:10:00:00 | 02JAN09:10:59:59 |
12 | 02JAN09:11:00:00 | 02JAN09:11:59:59 |
13 | 02JAN09:12:00:00 | 02JAN09:12:59:59 |
14 | 02JAN09:13:00:00 | 02JAN09:13:59:59 |
15 | 02JAN09:14:00:00 | 02JAN09:14:59:59 |
16 | 02JAN09:15:00:00 | 02JAN09:15:59:59 |
17 | 02JAN09:16:00:00 | 02JAN09:16:59:59 |
18 | 02JAN09:17:00:00 | 02JAN09:17:59:59 |
The following DATA step creates the FMDS
data set to define a custom interval FiscalMonth, which is appropriate for a business that uses fiscal months that start
on the 10th of each month. The SAME alignment option of the INTNX function specifies that the dates generated by the INTNX
function are the same day of the month as the date in the start
variable. For more information about the INTNX function, see SAS Date, Time, and Datetime Functions. The MONTH function assigns the month of the BEGIN
variable to the SEASON
variable. This specifies monthly seasonality.
options intervalds=(FiscalMonth=FMDS); data FMDS(keep=BEGIN SEASON); start = '10JAN1999'D; stop = '10JAN2001'D; nmonths = INTCK('MONTH',start,stop); do i=0 to nmonths; BEGIN = INTNX('MONTH',start,i,'S'); SEASON = MONTH(BEGIN); output; end; format BEGIN DATE.; run;
The difference between the custom FiscalMonth interval and a standard interval can be seen in the following example. The output shown in Figure 4.2 compares how the data are accumulated. For the FiscalMonth interval, values in the first nine days of the month are accumulated with the interval that begins in the previous month. For the standard MONTH interval, values in the first nine days of the month are accumulated with the calendar month.
data sales(keep=DATE sales); do date = '01JAN2000'D to '31DEC2000'D; month = MONTH(date); dayofmonth = DAY(date); sales = 0; if ( dayofmonth lt 10 ) then sales = month/9; output; end; format date monyy.; run;
proc timeseries data=sales out=dataInFiscalMonths; id DATE interval=FiscalMonth accumulate=total; var sales; run; proc timeseries data=sales out=dataInStdMonths; id DATE interval=Month accumulate=total; var sales; run;
data compare; merge dataInFiscalMonths(rename=(sales=FM_sales)) dataInStdMonths(rename=(sales=SM_sales)); by DATE; run;
title 'Standard Monthly Data vs. Fiscal Month Data'; proc print data=compare; run;
Figure 4.2: Fiscal Months Custom Interval
Standard Monthly Data vs. Fiscal Month Data |
Obs | date | FM_sales | SM_sales |
---|---|---|---|
1 | 10-DEC-1999 | 1 | . |
2 | 01-JAN-2000 | . | 1 |
3 | 10-JAN-2000 | 2 | . |
4 | 01-FEB-2000 | . | 2 |
5 | 10-FEB-2000 | 3 | . |
6 | 01-MAR-2000 | . | 3 |
7 | 10-MAR-2000 | 4 | . |
8 | 01-APR-2000 | . | 4 |
9 | 10-APR-2000 | 5 | . |
10 | 01-MAY-2000 | . | 5 |
11 | 10-MAY-2000 | 6 | . |
12 | 01-JUN-2000 | . | 6 |
13 | 10-JUN-2000 | 7 | . |
14 | 01-JUL-2000 | . | 7 |
15 | 10-JUL-2000 | 8 | . |
16 | 01-AUG-2000 | . | 8 |
17 | 10-AUG-2000 | 9 | . |
18 | 01-SEP-2000 | . | 9 |
19 | 10-SEP-2000 | 10 | . |
20 | 01-OCT-2000 | . | 10 |
21 | 10-OCT-2000 | 11 | . |
22 | 01-NOV-2000 | . | 11 |
23 | 10-NOV-2000 | 12 | . |
24 | 01-DEC-2000 | . | 12 |
25 | 10-DEC-2000 | 0 | . |
The next example uses custom intervals in the time function INTCK to omit holidays when counting business days. The result is shown in Figure 4.3.
options intervalds=(BankingDays=BankDayDS); data BankDayDS(keep=BEGIN); start = '15DEC1998'D; stop = '15JAN2002'D; nwkdays = INTCK('WEEKDAY',start,stop); do i = 0 to nwkdays; BEGIN = INTNX('WEEKDAY',start,i); year = YEAR(BEGIN); 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("COLUMBUS",year) and BEGIN ne HOLIDAY("VETERANS",year) and BEGIN ne HOLIDAY("THANKSGIVING",year) and BEGIN ne HOLIDAY("CHRISTMAS",year) then output; end; format BEGIN DATE.; run;
data CountDays; start = '01JAN1999'D; stop = '31DEC2001'D; ActualDays = INTCK('DAYS',start,stop); Weekdays = INTCK('WEEKDAYS',start,stop); BankDays = INTCK('BankingDays',start,stop); format start stop DATE.; run;
title 'Methods of Counting Days'; proc print data=CountDays; run;