When opening results from an information map query into a PivotTable in Excel, you might see unexpected values for summarized fields because, by default, information map queries are aggregated. The aggregations are performed on the SAS server, and then the aggregated values are passed to Excel. This design means that the size of the workbook is smaller; however, this design also means that Excel is not aware of the underlying data that is used to generate the aggregations.

Using an information map based on the SASHELP.CLASS table as an example, if you include the aggregated values of Age in a PivotTable and include only the Gender category (which is not unique), you see the results shown in Figure 1.

Figure 1. The average is calculated based on the aggregated data sent to Excel from the SAS server.

In order to see an average that takes the underlying data into consideration, you have two options, depending on your data source:

1. Click in the PivotTable, and select Filter & Sort in the Selection group on the SAS tab to open the Open Information Map dialog box.
2. Do one of the following:
• Deselect the Display aggregated values (group by category) option.
• If possible, select a unique category data item to include in the report. In this example, Name is a unique category. Your ability to use this option depends on whether your data includes a unique category.
3. Click OK.

Figure 2. With a unique category data item (Name) included in the report, Excel can use all of the underlying data to calculate the average.

Operating System and Release Information