Date Intervals, Formats, and Functions


Custom Time Intervals

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:

  1. 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);
    
  2. 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 5.1.

Figure 5.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 5.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 5.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 5.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;

Figure 5.3: Bank Days Custom Interval

Methods of Counting Days

Obs start stop ActualDays Weekdays BankDays
1 01JAN99 31DEC01 1095 781 757