Understanding how numeric date variables that are defined as categories with a user-defined format are handled when exported to Microsoft Excel


SAS® Information Map Studio enables you to change the classification of a numeric variable to a category data item. These data items can also have applied user-defined formats. This usage note explains how these two settings impact the results when they are exported from SAS® Web Report Studio 4.2 running with the third maintenance release for SAS 9.2 (TS2M3) or later.

This usage note focuses on numeric date variables. For an explanation of the exporting behavior when the numeric data item is not a date, see SAS Note 39963.

This example focuses on a numeric variable named Date. The following display shows the Date variable defined as a numeric in the table metadata.

In Design, SASApp is selected, then Test, then FORMATSAMPLE, opening up date and organization

This view shows that the data item is being created as date when selected for the information map.

For Type, Date is selected

The display below shows Date defined as a Category data item in an information map. A user-defined format is also applied.

Classifications, Aggregations, For... is selected, and Selected Data Items is populated with Date.

When this data item is used in a SAS Web Report Studio report, the user-defined format is applied to the results.

View is open, showing the data in date format

When you export this report to Excel, you will be able to filter values based on the character values specified by the user-defined format, rather than based on the original numeric values. However, sorting is performed on the original numeric value. Sorting is handled in this way so that the dates sort in chronological order and not alphabetical order.

The first column of an Excel sheet is open, and a popup window with sorting options is open

If you are running a release prior to the third maintenance release for SAS 9.2 (TS2M3), the user-defined format is not used in Excel, and filtering is done on the original numeric values.