Modifying Aggregation Tables

Delete an Aggregation Table from an Aggregation Transformation

To delete an aggregation table from an Aggregation transformation, perform the following steps:
  1. Locate the Aggregation transformation that generates the aggregation table that you want to delete. To do so, 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.
  2. Right-click the aggregation table that you want to delete.
  3. From the drop-down list, select Delete. The table is removed from the Aggregation transformation that is displayed in the process flow diagram. However, the metadata for that table and the library are not deleted from the IT data mart. For information about this topic, see Delete an Aggregation Table from the IT Data Mart.
In order for your changes to take effect, you must redeploy the job. For information about redeploying jobs, see Redeploy All Jobs on the Server.
CAUTION:
To avoid processing errors, delete any reference to this table, as a source or target for any transformation in any other job.
For example, Information Map transformations that use the deleted table as a source table should be deleted.

Delete an Aggregation Table from the IT Data Mart

To delete an aggregation table from an IT data mart, perform the following steps:
  1. Locate the aggregation table that you want to delete. To do so, from the IT Data Marts tree, navigate to the IT data mart that contains the job where the aggregation table is generated.
  2. Expand the IT data mart tree and navigate to the aggregation table that you want to delete. Right-click the table.
  3. From the drop-down list, select Delete.
  4. Click Yes to confirm that you want to delete this table. The table is removed from the IT data mart. However, the associated library is not deleted.
CAUTION:
Deleting a table can break join columns if the table that you delete is the source for another table.

Delete an Aggregation Table with Contents

To delete with contents an aggregation table from an IT data mart, perform the following steps:
  1. Locate the aggregation table that you want to delete. To do so, from the IT Data Marts tree, navigate to the IT data mart that contains the job where the aggregation table is generated.
  2. Right-click the aggregation table that you want to delete.
  3. From the drop-down list, select Delete with Contents.
  4. Click Yes to confirm that you want to delete this table. The table is removed from the IT data mart. However, the associated library is not deleted.
CAUTION:
Deleting a table can break join columns if the table that you delete is the source for another table.

Erase an Aggregation Table

Erasing an aggregation table deletes the physical table, its contents, and its metadata. To erase an aggregation table, perform the following steps:
  1. In a tree view of SAS IT Resource Management, locate the aggregation table that you want to erase.
  2. Right-click the aggregation table that you want to erase. The aggregation job that includes the aggregation table must be closed and currently not in use before erasing.
  3. Select Erase.
  4. Click Yes to confirm that you want to erase this aggregation table.
CAUTION:
If you erase an aggregation table that serves as a source table in other jobs, then these associated jobs might not execute because they are missing a source table.
Simply erasing an aggregation table does not affect the metadata of the associated aggregation or information map jobs. However, if you erase an aggregation table and then rerun the aggregation job, then the data for the deleted aggregation table is not created and the jobs that used that table will fail due to a missing source table.

Modify a Simple or a Summarized Aggregation Table

If you need to modify an aggregation table, for best results do so from within the aggregation job. (If you want to edit an aggregation table that was created by the Simple Aggregation Table wizard or the Summarized Aggregation Table wizard, you must first save the job that created that aggregation table.) To modify a simple or a summarized aggregation table, perform the following steps:
  1. Locate the Aggregation transformation that generates the aggregation table that you want to modify. To do so, 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.
  2. Right-click the aggregation table that you want to modify. From the list that displays, select Edit table type Aggregation Table.
    Note: If the table is a simple aggregation table, the list displays Edit Simple Aggregation Table. If the table is a summarized aggregation table, the list displays Edit Summarized Aggregation Table. However, if the aggregation table is not associated with an Aggregation transformation, then the Edit table type Aggregation Table option will not be available.
  3. Use the Jump to Page option to go directly to the page of the aggregation wizard that you want to change. Use the drop-down list to display the pages to which you can go directly.
    Jump to Percent Change Page for a Summarized Aggregation Table
    Jump to Percent Change Page for a Summarized Aggregation Table
  4. Select the page that contains the specifications that you want to modify and click Next.
  5. Modify the contents of the page that you selected. (For information about which fields can be changed, follow the instructions that apply to the creation of the type of table that you want to modify.)
    Note: If you try to delete a column that is used to create a statistic, percent change, rank, or join column, a message is displayed that lists the column, where it is used, and its table name. If a column was deleted by means of the Columns tab of the Properties dialog box, then the next time the aggregation wizard is opened on that table, a message is displayed that indicates any columns whose source column is no longer available. Click Yes to delete the columns from the table and continue to edit the table. Click No to keep the columns. In that case, the wizard is not opened.
  6. Click through the remaining pages of the wizard until the summary page appears.
  7. Click Finish to save your changes and return to the process flow diagram.
In order for your changes take effect, you must redeploy the job. For information about redeploying jobs, see Redeploy All Jobs on the Server.

Purging the Content of an Aggregation Table

Purge Data from an Aggregation Table from the Process Flow Diagram

To purge data from an aggregation table, perform the following steps:
  1. Locate the Aggregation transformation that generates the aggregation table that you want to modify. To do so, 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.
  2. Right-click the aggregation table whose data you want to purge.
  3. From the drop-down list, select Purge.
  4. Click Yes to confirm that you want to purge the data from this aggregation table.

Purge Data from an Aggregation Table on the IT Data Marts Tab

You can also purge the contents of an aggregation table by performing the following steps:
  1. Locate the Aggregation transformation that generates the aggregation table that you want to modify. To do so, from the IT Data Marts tree, navigate to the IT data mart that contains the job where the Aggregation transformation is located.
  2. Right-click the aggregation table whose data you want to purge.
  3. From the drop-down list, select Purge.
  4. Click Yes to confirm that you want to purge the data from this aggregation table.

Renaming an Aggregation Table

CAUTION:
You can change the name of the physical aggregation table only by using the Edit Aggregation wizard.
Changing the name of the table on the General tab of the Properties dialog box does not change the name of the physical table.
To rename a simple or a summarized aggregation table, perform the following steps:
  1. Locate the Aggregation transformation that generates the aggregation table that you want to modify. To do so, 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.
  2. Right-click the aggregation table that you want to rename.
  3. From the drop-down list, select Edit <aggregation-type> Aggregation Table, where aggregation-type is Summarized or Simple. This action opens the Summarized Aggregation wizard or the Simple Aggregation wizard.
  4. To change the name of the table in the metadata, enter the new name of the aggregation table in the Name field on the first page of the wizard.
    To change the name of the physical table, enter the new name of the aggregation table in the SAS Table Name field.
  5. Click Next to proceed through all the pages of the wizard. Then click Finish.
  6. Click OK to save your changes and return to the process flow diagram.
Note: This task changes the name of the table in the metadata. The name of the physical SAS data set is changed when you click Finish. In order for your changes to take effect, you must redeploy the aggregation job. In addition, even though the metadata linkage to that renamed table still exists, it is advisable to redeploy the information map job that uses the renamed table as input. For more information about redeploying jobs, see Redeploy All Jobs on the Server.
Copyright © SAS Institute Inc. All rights reserved.