Working with Aggregation Transformation Filters

About Aggregation Transformation Filters

A filter can be used to limit the amount of data that is input to an individual aggregation table. You can define a filter to be used in an Aggregation transformation, modify or edit the filter, and delete it from an Aggregation transformation. A filter that is specified for an Aggregation transformation can be applied to any of the aggregation tables that are being specified in the Aggregation transformation. However, the filter will not affect any of the data that is already in the tables that were created or updated before the application of the filter. If you define a filter to be used in an Aggregation transformation, modify an existing filter in an Aggregation transformation, or delete a filter from an Aggregation transformation, the existing aggregation tables are not automatically regenerated.

Note: Filters are used only when data is read into the aggregation table. If you modify a filter, the data that is already in the table remains unchanged.

After a filter has been added to an Aggregation transformation, it can be applied to the input of any aggregation table within that transformation. For both the Simple Aggregation wizard or the Summarized Aggregation wizard, you can select an input filter on the Select filter and column for data duplication page of that wizard. When you are using the Aggregations through Templates wizard, if the aggregation that you select uses a filter, it will be added to the specification automatically.

Access the ITRM Options Tab on the Properties Dialog Box of an Aggregation Transformation

To define, modify, or delete a filter for an Aggregation transformation, you must first access the ITRM Options tab on the Properties dialog box of that Aggregation transformation. To do so, perform the following steps:

From the IT Data Marts tree, navigate to the IT data mart that contains the job where the Aggregation transformation is located.
Double-click the job to open it on the Diagram tab of the Job Editor window, and locate the appropriate Aggregation transformation.
Right-click the Aggregation transformation. From the drop-down list, select Properties.
Select the ITRM Options tab to open the grid of input filters. The List of Filters group box displays the name and expression of input filters that are defined for this Aggregation transformation.

Define a Filter for an Aggregation Transformation

To define an input filter for an Aggregation transformation, perform the following steps:

Access the ITRM Options tab on the Properties dialog box of the Aggregation transformation for which you want to define a filter. To do so, see the preceding topic.

Click New to display a new line in the grid on which you can enter a new filter. The default name of this filter is "Untitledn," where n is the nth filter that exists for this Aggregation transformation.

The following display of the ITRM Options tab shows that a new filter is being defined for the SMF Jobs Aggregation.

List of Filters Group Box
Enter the name of the filter. Consider giving the filter a name that is unique within this Aggregation transformation.
Enter the expression that specifies the filter. You can enter the expression directly in this field, from a formula, or by using the Expression Builder functionality of SAS Data Integration SAS IT Resource Management. For information about how to use this functionality, see About the Expression Builder.
If you want to purge the source table upon successful creation of aggregation tables, click the corresponding check box.
When you are satisfied with the definition for the filter, click OK to save your work and return to the process flow diagram.

Delete a Filter from an Aggregation Transformation

To remove a filter from an Aggregation transformation, you must first remove the filter from all the aggregation tables that use it. (For information about how to do so, see Delete a Filter from an Aggregation Table.)

Access the ITRM Options tab on the Properties dialog box of the Aggregation transformation from which you want to delete a filter. To do so, see Access the ITRM Options Tab on the Properties Dialog Box of an Aggregation Transformation. Then perform the following steps:

Select the filter that you want to delete.
Click Delete. If the filter that you selected is used in another table, a warning message is displayed that lists the tables where the filter is used. In that case, you must delete the filter from those tables.
Click OK to save your changes and return to the process flow diagram.

Note: In order to apply the changes that you made, you must redeploy the jobs that use that filter. For information about deploying jobs, see Redeploy All Jobs on the Server.

Delete a Filter from an Aggregation Table

An aggregation filter that is being used by an aggregation table cannot be deleted from an Aggregation transformation. If you attempt this action, a message box appears that identifies the aggregation tables that are using the filter. To delete the input filter from the Aggregation transformation, you must first remove it from all aggregations where it is being used.

To remove a filter from an aggregation table, perform the following steps:

From the IT Data Marts tree, navigate to the IT data mart that contains the job where the Aggregation transformation is located. Double-click the job to open it on the Diagram tab of the Job Editor window.
Right-click the aggregation table that uses the input filter. From the drop-down list, select Edit table type Aggregation Table.

Note: If the table is a simple aggregation table, the drop-down list displays Edit Simple Aggregation Table. If the table is a summarized aggregation table, the drop-down list displays Edit Summarized Aggregation Table.
Use the Jump to Page option to go directly to that page of the wizard. The drop-down list shows the pages that you can go to. Select Apply Filter and click Next.
In the Select filter field, the drop-down list shows the filters that can be applied. You can choose another filter to apply to the table, or else the (None) option, which removes the filter from this table.

Note: Only one filter can be applied to an aggregation table.
Click through the remaining pages of the wizard until the summary page displays.
Click Finish to save your changes and return to the process flow diagram.

Modify a Filter of an Aggregation Transformation

To modify or edit a filter that is associated with an Aggregation transformation, perform the following steps:

Access the ITRM Options tab on the Properties dialog box of the Aggregation transformation for which you want to modify a filter. To do so, see Access the ITRM Options Tab on the Properties Dialog Box of an Aggregation Transformation.
Select the filter that you want to modify. You can change the name and the expression of the filter. You can also change the specification about purging the input table after the aggregation tables have been created.
- To change the name of the filter, select the Name field of the filter to highlight it. Then enter the new name of the filter.
- To change the expression, select the Expression field to highlight it. Then enter the modified expression that specifies the filter. You can enter the expression directly in this field or you can use the Expression Builder functionality of SAS Data Integration SAS IT Resource Management. For information about how to work with the Expression Builder in SAS IT Resource Management, see About the Expression Builder.
- To change the specification about purging the input table after the aggregation tables are created, click the corresponding check box. (A checked box means that the source table will be purged.)
When you have finished modifying the filters, click OK to save your changes and return to the process flow diagram.

About the Expression Builder

To invoke the Expression Builder, double-click the Expression field.

Invoking the Expression Builder

Then, double-click the icon that contains the ellipsis (...). The Expression Builder window appears and enables you to specify a filter.

Note: When invoked from the Add Filter dialog box, the Expression Builder window consists of two tabs: Functions and Data Sources.

Expression Builder Window with Functions Tab Selected

Functions Tab of the Expression Builder Window

The Data Sources tab of the Expression Builder window lists the columns of the table that is the input to this Aggregation transformation.

Expression Builder Window with Data Sources Tab Selected

Data Source Tab of the Expression Builder

You can select any of these columns from the list of data sources to be an operand in the expression that you are building.

For information about the Expression Builder window, click the F1 key or Help from within that window.

Note: The syntax of the expression that specifies the filter must conform to the rules for an IF statement and should result in a nonzero value for true and zero value for false. (Do not code the word IF; code the expression only.) Certain operations that are valid only in a WHERE clause fail if they are used in a filter. For information about the IF and WHERE statements, see the Statements chapter of the SAS Language Reference: Dictionary.

When you are satisfied with the expression, click OK in the Expression Builder window to close that window and place the expression in the appropriate field of the filter.