• Print |
• E-mail  |
• Bookmark

By default, SAS Web Report Studio sorts category data items in ascending alphabetical order. This sample uses the SASHELP.SHOES table to demonstrate how you can reshape the data in order to define a custom sort order. Specifically, the sample shows you how to create a measure for a category data item with the order that you would like to use. You would then apply a custom format so the values can be displayed in your preferred sort order.

Here is how the values for Product appear in a list table when default sorting is used.

Note: For more information about sorting in SAS Web Report Studio, see SAS Note 31563.

Create the Format

The following code is used to create a user-defined format that places the Product variable values in a specified numeric order.

Note: The SAS Business Intelligence clients expect formats in a specific location. The default location is \Lev1\SASMain\SASEnvironment\SASFormats. The following code uses PROC CATALOG in order to copy the format to this location.

libname exists 'c:\';
proc format lib=exists;
value productfmt
           1="Boot"
	   2="Men's Casual"
	   3="Women's Casual"
	   4="Men's Dress"
	   5="Women's Dress"
	   6="Sandal"
	   7="Slipper"
	   8="Sport Shoe";


options fmtsearch=(exists.formats);
libname new 'c:\new';   /* Create a location to store reshaped data */
data new.newshoes;
 set sashelp.shoes; /* the original data set */
         if PRODUCT = "Boot" then NEWPRODUCT=1;
	 if PRODUCT = "Men's Casual" then NEWPRODUCT=2;
	 if PRODUCT = "Women's Casual" then NEWPRODUCT=3;
	 if PRODUCT = "Men's Dress" then NEWPRODUCT=4;
	 if PRODUCT = "Women's Dress" then NEWPRODUCT=5;
	 if PRODUCT = "Sandal" then NEWPRODUCT=6;
	 if PRODUCT = "Slipper" then NEWPRODUCT=7;
	 if PRODUCT = "Sport Shoe" then NEWPRODUCT=8;

format NEWPRODUCT productfmt.;
run;

/* Change the path below to your specific BI path location */

libname sasenv '<your-physical-bienv-location>\Lev1\SASMain\SASEnvironment\SASFormats';
proc catalog cat=exists.formats;
   copy out=sasenv.formats;
run;

Tip: You can also use an input control data set in order to create the format. See Example 5: Creating a Format from a Data Set in the Base SAS 9.2 Procedures Guide.

Use the Format

1. In SAS^® Management Console, register the NEWSHOES table.
2. In SAS Information Map Studio, insert NEWSHOES into a new map.
3. Instead of adding the original Product variable as a data item, use the new data item NEWPRODUCT.
4. Go into the properties for NEWPRODUCT and change the type from Numeric to Category. Check at the bottom of the dialog box to see that the user-defined format that you created is selected.
5. In SAS Web Report Studio, select the NEWPRODUCT data item from the information map that is based on the NEWSHOES table. (In the Select Data dialog box, you can select NEWPRODUCT and click on the pencil icon to rename the data item back to Product.)
6. Add a list table to the report.
7. View the report and sort the NEWPRODUCT column in ascending order. You should see the sort results based on the custom order defined by the applied format.

Additional Documentation

For more information about using user-defined formats in a SAS BI Server environment, see "Working with User-Defined Formats" in the SAS 9.1.3 Intelligence Platform: Data Administration Guide . Also see SAS Note 12157.

For a list of available documentation for SAS Web Report Studio, see http://support.sas.com/documentation/onlinedoc/wrs/index.html

For a list of available documentation for SAS Information Map Studio, see http://support.sas.com/documentation/onlinedoc/ims/index.html

Operating System and Release Information