Pivot Tables Overview
Pivot tables is a data summarization tool used in MS Excel used for data visualization.  It can automatically sort, count, and total the data stored in one spreadsheet and create a second table displaying the summarized data. Since this is viewed on the client side within Excel, the administrator can prepare and define the attributes of the pivot table as it relates to the selected dataset ahead of time.  That way, when the user selects the pivot table, it will bring it up and display it within Excel with all its cross table view.  This can be accomplished with the PIVOTAB tool on the server.

Pivot Tables and SAS Data
A SAS dataset is stored in a physical path which is associated with library.  It may have a SAS format associated with it to decode its control terms.  In the same way, pivot tables can be defined with the associated library and format catalog path since this is required to define the pivot table attributes as it pertains to a SAS dataset.

The administrator can define the library and path to formats by navigating through the “…” buttons. 

Defining Pivot Table Attributes
The process of defining attributes of a pivot table is to first select the dataset to have the pivot table associated with.  The first two types of attributes that is defined next is the Row and Column variables.  To do this, select the variables corresponding to the row, select the type column to be row and then add it to the definition with the “Add” button.

This would then be repeated for a different variable within the same dataset for Column, then Data and then optionally Page.

Assigning Pivot Table Names
The short name for the pivot table is identified by the SAS dataset name.  Each pivot table can be assigned a name which is like a descriptive label of the pivot table.  By default, this is the SAS dataset label but it can be updated to be what ever is meaningful in describing the pivot table.

Defining Pivot Table in Batch
The definition of pivot tables such as row and column attributes can also be accomplished in batch mode in a macro program.  The administrator can generate a template macro by clicking on the “Save Code” button.

This will generate a SAS macro program with the selected pivoted table definition to a specified program location.  The administrator can then edit this macro program to perform additional definition in batch mode without having to perform many clicks.