Date Intervals, Formats, and Functions

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:

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.

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;

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;