33017 - Defining a custom sort order for use in tables in a SAS® Web Report Studio report

Sample 33017: Defining a custom sort order for use in tables in a SAS® Web Report Studio report

Overview

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 then apply a custom format so that 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.

image label

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

Create the Format

The sample code performs the following tasks:

Creates a user-defined format named productfmt that places the Product variable values in a specified numeric order.
Applies the new format to a new variable named NEWPRODUCT in a copy of the SHOES table.
Uses PROC CATALOG to copy the format to a catalog named formats.sas7bcat in the default location used by the SAS® Business Intelligence clients. That default location is <SAS-config-dir>/Lev1/SASApp/SASEnvironment/SASFormats. For information about making user-defined formats available, see SAS Note 40103.

/* User ID executing the code must have permission to write to this location */

libname exists 'c:\temp';

/* Create the format */

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);

/* Create a libref to an existing folder location*/

libname new 'c:\new';  

/* Apply the format to a new variable in a copy of SHOES */

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 to point to your default catalog location*/
libname APFMTLIB '<SAS-config-dir>\Lev1\SASApp\SASEnvironment\SASFormats';

proc catalog cat=exists.formats;

   copy out=APFMTLIB.formats;

run;

Use the Format

In SAS® Management Console, register the NEWSHOES table.
In SAS® Information Map Studio, insert NEWSHOES into a new information map.
Instead of adding the original Product variable as a data item, use the new data item NEWPRODUCT.
Edit the properties for NEWPRODUCT. Change the type from Numeric to Category. At the bottom of the dialog box, verify that the user-defined format that you created is selected.

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 the pencil icon to rename the data item back to Product.)
Add a list table to the report.
View the report and explicitly sort the NEWPRODUCT column in ascending order. (To sort on unformatted values, you must apply an explicit sort.) You should see the sort results based on the custom order defined by the applied format.

image label

Tip: To align values to the left, right-click the column and select Align Left.

Additional Documentation

For more information about using user-defined formats in a SAS® Enterprise BI Server environment, see SAS Note 40103.
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

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Date Modified:	2008-09-02 13:37:48
Date Created:	2008-08-20 12:35:36

Product Family	Product	Host	Product Release		SAS Release
Product Family	Product	Host	Starting	Ending	Starting	Ending
SAS System	SAS Web Report Studio	Microsoft Windows Server 2003 Standard Edition	3.1		9.1 TS1M3 SP4
		Microsoft Windows XP Professional	3.1		9.1 TS1M3 SP4
		64-bit Enabled AIX	3.1		9.1 TS1M3 SP4
		Windows Vista	3.1		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	3.1		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	3.1		9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	3.1		9.1 TS1M3 SP4
		Microsoft Windows NT Workstation	3.1		9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	3.1		9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	3.1		9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	3.1		9.1 TS1M3 SP4
		Microsoft® Windows® for x64	3.1		9.1 TS1M3 SP4
		64-bit Enabled Solaris	3.1		9.1 TS1M3 SP4
		HP-UX IPF	3.1		9.1 TS1M3 SP4

Support

Sample 33017: Defining a custom sort order for use in tables in a SAS® Web Report Studio report

Overview

Create the Format

Use the Format

Additional Documentation

Operating System and Release Information