Handling Holidays

Overview of Handling Holidays

Example of Handling Holidays

Overview of the Example

Creating the Format

Creating the Formula

(Optional) Including the Holiday Specification in the SHIFT Formula

Define an Input Filter for Holiday Data in an Aggregation Transformation

Use the Holiday Filter with One or More Aggregation Tables

Overview of Handling Holidays

SAS IT Resource Management does not implement any specific holiday support. However, you can use the features of SAS IT Resource Management to prevent data that was generated on a holiday from being processed into an aggregation table. To accomplish this goal, perform the following steps.

If your site’s holidays can be specified as individual dates, define a SAS format to identify them. To do this, create a format that defines whether a given date is a holiday. For best results, create this format in the Admin library of the IT data mart. (This library is included in the SAS format search concatenation.)

You might want to specify holidays by using different date or datetime measurements (such as 8 AM on a given date). To do so, keep the entire holiday list in the source code of a formula. (This method is explained in the next step.)

Note: It is preferable to use formats to define holidays because formats can be easily revised to reflect changes to the list of holidays. In addition, you do not need to redeploy any jobs. If you use formulas to define your list of holidays, any changes to that list require that jobs be redeployed.
Define a formula in the SAS IT Resource Management client that identifies holidays. This formula will be used to create a computed column in the staged table.

To create a formula, select FileIT Resource ManagementFormula. The New Formula wizard opens. On the General Information page of the wizard, specify a name for the formula. On the Expression page, enter the code to assign the Y or N value to the computed column. If you created a format in the previous step, use it in this formula.

In some cases, the holiday rule is not a simple assignment statement, based on a format. The rule that identifies whether data is generated on a holiday might require a more complex expression.

Note: If an expression is revised in order to accommodate a change in the list of holidays for a site, you must redeploy the jobs that use that formula. For information, seeAbout Formulas.
Specify a distinct value for SHIFT if the data is for a holiday. To do this, you must modify the SHIFT formula to include the code to handle the holiday.

Note: Formulas cannot use other formulas because the order of computation is not guaranteed.

For information about the effects of changing a formula expression, see Consequences of Changing a Formula.

Note: To propagate any change to formulas used in existing jobs, you must redeploy those jobs.
To define a computed column in your staged table, you can use the holiday formula. You can also use the SHIFT formula for this purpose. This computed column specifies whether the data in this row of the table was generated on a holiday. For information about adding a computed column to a staged table, see Modify a Staged Table.
Define an input filter that prevents holiday data from being processed into the aggregation tables. You can define filters for the aggregation table in the Aggregation transformation that generates the table. To do so, perform the following steps:
1. Double-click the job that contains the Aggregation transformation. The job opens in the Diagram tab of the Job Editor window
2. Right-click the Aggregation transformation. From the Properties dialog box, select the ITRM Options tab. On this tab, you can define a filter by clicking New. In the List of Filters box, you can add a filter that can be used for this Aggregation transformation.
  
  ITRM Options Tab
  
  Note: Set the filter so that data that satisfies its conditions is the only data that is processed.
  
  After you specify the holiday filter, click OK and save the job.
After the filter is added to the Aggregation transformation, you can use it with one or more aggregation tables that are generated by this transformation. To apply a filter to an aggregation table, do one of the following two steps.
- For a new aggregation table that you want to create, right-click the Aggregation transformation and select Add Aggregation Table from the Properties dialog box. Select the type of table that you want to create (summarized or simple.)
  
  Respond to the prompts of the wizard that opens. On the second page of the wizard, you can select the holiday filter that you want to use. When you have completed entering the information for the table, click Finish and save the job.
- For an existing aggregation table, right-click the table and select Edit Summarized Aggregation Table or Edit Simple Aggregation Table from the Properties dialog box. Then navigate to the Apply Filter page of the wizard and select the holiday filter that you want to use. When you have completed editing the table, click Finish and save the job.
  
  Note: Implementing a filter does not change existing data in a table.
Note: Newly created jobs or jobs that have been changed must be saved and redeployed.For information about redeploying jobs, see Redeploy All Jobs on the Server.

Example of Handling Holidays

Overview of the Example

In this example, a format is specified defining Christmas Day as a specific holiday. The formula that is created uses the holiday format to specify a Holiday column. The Holiday column is added to a staged table as a computed column. This computed column will contain a Y for every row of data whose date column corresponds to the definition of the holiday.

After the staged table is populated with the computed Holiday column, a filter is created in the Aggregation transformation. The filter prevents holiday data from being processed into the aggregation table. This filter can be implemented for any aggregation table that is created or updated by that Aggregation transformation.

Creating the Format

The following code creates a SAS format that specifies the Christmas holiday for 2010 and 2011. The resulting value of holiday is a single-character of ‘Y’ or ‘N’ that indicates whether the date of the data is a holiday.

libname ADMIN BASE "c:\datamarts\smfasw\admin";
       proc format library=admin;
       value holiday (default=1 min=1 max=1)
                     '25Dec2010'd = 'Y' 
                     '25Dec2011'd = 'Y' 
                      other =  'N';
       run;

Creating the Formula

The following formula uses the value of the holiday format to define the Holiday column:

Holiday = PUT(DAYDATE,holiday.);

Note: Add the Holiday column to the staged table as a computed column. This column can be used as a filter to exclude from the Aggregation transformation all data that was generated on a holiday. The filter ensures that only data that is not a holiday is processed.

(Optional) Including the Holiday Specification in the SHIFT Formula

Suppose your SHIFT formula currently has the following expression:

if weekday(datepart(datetime)) in (1,7) then shift = '3'; 
  else if timepart(datetime) < '08:00:00't or
          timepart(datetime) >= '17:00:00't 
                                 then shift = '2';
                                 else shift = '1';

In order to accommodate the holiday specification, change this expression as follows:

if put(DAYDATE,holiday.) eq ‘Y’ then shift = ‘0’; 
   else if weekday(datepart(datetime)) in (1,7) then shift = '3' ;
        else if timepart(datetime) lt '08:00:00't or 
                timepart(datetime) ge '17:00:00't 
                                       then shift = '2'; 
                                       else shift = '1';

Define an Input Filter for Holiday Data in an Aggregation Transformation

Open the job that contains the appropriate Aggregation transformation. Right-click the transformation. On the Properties dialog box of the Aggregation transformation, select the ITRM Options tab. Click New to define the following filter. In the following display, the Holiday filter has this expression:

Holiday eq 'N'

ITRM Options Tab

Click OK.

Use the Holiday Filter with One or More Aggregation Tables

The Aggregation wizards enable you to specify the Holiday filter for your aggregation table. The following display shows the page of the Aggregation wizards where you can select the filter.

Note: The filter is an inclusive filter. Data that satisfies the conditions of the filter is processed into the aggregation table.

Selecting a Holiday Filter

After you select the input filter, save the job and redeploy it for execution.

Note: The input filter does not affect data that is already in the aggregation table. However, it filters any new incoming data to the aggregation table.