SAS Visual Analytics: How to add Ranks to an Object

[#1]

Ranks allow you to focus on the top or bottom part of your data, based on one or multiple categories and a measure. This feature is useful for highlighting key insights, such as the best-performing products, the least profitable regions, or the top-spending customers. SAS Visual Analytics makes it very easy to add a rank to an object. In this post, I will explore how to add a rank and explain the difference between:

1. Adding a rank for one or multiple categories
2. Adding a rank for all visible categories (only available for list tables)
3. Adding a detail rank (only available for list tables, bubble plots and scatter plots).

Note that it is currently not possible to add a rank by group in SAS Visual Analytics – this has to be done beforehand, for example in SAS Studio. See: SAS Help Center: Ranking Values within BY Groups.

Setting up

You can skip this section unless you want to follow along in your own SAS Visual Analytics environment. In this post, I use the SASHELP.ORSALES dataset. If you want to follow along, you can load this dataset by going to SAS Studio (Develop Code and Flows from the SAS Viya Applications Menu):

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

Then click, on Program in SAS:

The following code establishes a new CAS session, loads the SASHELP.ORSALES table into the CASUSER caslib, and promotes it to global scope so it is visible across all CAS session. Paste this code into a new program, and then click on Run.

cas;

proc casutil;

     load data=sashelp.orsales outcaslib=”casuser” promote;

run;

We can now return to SAS Visual Analytics. To do this, click on Explore and Visualize from the Applications Menu.

Click on New report. I can now add the ORSALES table that I just loaded. To do this, click on Add Data from the Data pane on the left.

The easiest way to find the ORSALES table is to search for it. Select it and click on Add.

Understanding the Source Table

In order to choose the correct type of rank, you need to understand the structure of your source table. To do this, I will view the source table. From the data pane on the left, click on the Data source menu, and then on View ORSALES source table.

To see the most profitable products, click on the header of the Profit column twice to sort by it in descending order.

Note that I have one observation per Product Group and Quarter. There are multiple Product Groups in a Product Category. I will use Product Category in the list tables below. Understanding your source table is crucial to apply the right type of rank.

Understanding ranks

With a rank, I can display the data pertaining to the top or bottom portion of data. I need two things for a rank, a (combination of) categories, and a measure. For the example above, I could rank

1. Product Category (the category) by Profit (the measure) and display the top 3 Product Categories by Profit. This is explored in the following section. I then explore what happens when I add rankings for multiple categories.
2. Product Category and Quarter (a combination of both visible categories in the list table) by Profit (the measure) and display the top 3 Product Category and Quarter combinations by Profit. This can be done by ranking all visible categories and is explained in the corresponding section. This option is only available for list tables.
3. Display the top 3 observations (a combination of all categories in the source table) by Profit. This is done using a detail rank and explained in the last section. This option is only available for list tables, bubble plots and scatter plots.

Add a rank for a specified category

In this section, I will add a rank to a list table for a specified category: I will display the top 3 Product Categories based on Profit. First, I will add a list table using the Objects pane on the left:

I then assign Product Category, Quarter and Profit in USD to the list table.

To display the top 3 Product Categories, I add a rank. From the Ranks pane on the right, click on New rank, and then Product Category:

Here are the following options:

Item	Explanation
Subset	Top Count: Show the data for the highest N values Top Percent: Show the top N% of the data Bottom Count: Show the data for the lowest N values Bottom Percent: Show the bottom N% of the data
Count	N: the number of values to return
Rank By	The measure whose aggregated values are used for the ranking.
Include Ties	Unchecked: tied observations are counted separately towards the number of observations specified in the subset option. Checked: tied observations are not counted separately. You might see more observations than what you had specified in the subset option.
Include All Other	Aggregates all the data that are not ranked and displays it with the label All Other. This option is not available when ranking all visible categories, nor for detail ranks.

In this example, I selected

• Subset: Top Count
• Count: 3
• Rank By: Profit

and do not include ties, nor all other.

Essentially, think of the rank returning the top List Table’s results.  It will return the top 3 Product Category values based on the aggregation of Profit, in this case it is using the default SUM aggregation.

Now look at the second List Table, with Product Category, Quarter and Profit displayed.  Notice that with the same Rank applied, I have the same 3 Product Categories:  Assorted Sports Articles, Clothes, and Outdoors, but the Quarter adds additional information.  This is not implying that for each quarter these are the top 3 Product Categories, but for all of the data, these are the top 3 Product Categories.

If you want to rank multiple categories, you could repeat these steps for each category you want to rank. However, this will create a separate ranking for each category. In the example below, I add a ranking to display the top 3 Quarters by Profit. The following happens:

1. The existing rank on Product Category displays the top 3 Product Categories.
2. At the same time, the ranking on Quarter displays the top 3 quarters, not taking the Product Category ranking into account.
3. The resulting list table displays all combinations of the top 3 Product Categories and top 3 Quarters by Profit, that is 3 times 3, or 9 rows.

In other words, the combined ranking displays a row if it is among the top 3 Product Categories OR top 3 quarters by Profit. The rankings are combined using the logical operator OR, not AND.

If you wish to see the top 3 Quarter-Product Category combinations, you can create a new data item concatenating Quarter and Product Category. In the Data pane, click on New data item and Calculated item.

Enter the following formula:

Concatenate(‘Product Category’n, ‘Quarter’n)

Follow the same steps to add a ranking for this new calculated data item. The resulting list table displays 3 rows:

As mentioned earlier, note that it is currently not possible to add a rank by group in SAS Visual Analytics, which would allows us to see the top 3 Quarters for each Product Category (and vice versa: the top 3 Product Categories for each Quarter). This can be done in SAS Studio. See: SAS Help Center: Ranking Values within BY Groups.

Ranking all visible categories

Alternatively, you may want to rank all visible categories. Using the analogy from above, this will combine the ranking using AND. This option is only available for list tables and will rank all the categories assigned to the list table, except those assigned to the hidden role. If you are following along, create a new page called Rank by all visible categories, add a list table displaying Product Category, Quarter and Profit. Then, from the rank pane on the right, click on New rank and All visible categories.

In the following rank menu, I make the same selections as in the previous section. For an explanation of the available options, see the table in the previous section.

Recall the source table had multiple observations per Product Category and Quarter. In the background, SAS Visual Analytics aggregates the Profit for each combination of Product Category and Quarter, only returning the top 3.

Unlike in the previous section, this list table displays only 3 observations: the top 3 combinations of Product Category and Quarter by Profit. A list table aggregates data so only one row is displayed for each combination of category values assigned to it (unless the detail data is checked in the list table options), in this case: 1 row for each Product Category and Quarter combination. This matches the ranking categories, since I ranked by All visible categories.

Detail Rank

In some situations, you don’t want your ranking to be based on an aggregation. In this case, you can use a detail rank. This is only available for list tables, bubbles plots and scatter plots.

If you are following along, create a new page called Detail Rank, add a list table to it with Product Category, Quarter and Profit. Then, click on the rank pane on the right, and select New rank, and Detail rank. For an explanation of the available options, see the table above.

You will notice that I again have 3 observations in the resulting table. If you look closely, the values for Profit are different compared to the previous section. In the previous section, the ranking with all visible categories displayed the sum of Profit over all observations from the top 3 Quarter and Profit Category combinations. A detail rank returns the top 3 observations by Profit. This is equivalent to sorting the source table by Profit in descending order, and only keeping the top 3 observations (see below).

Conclusion

SAS Visual Analytics makes it very easy to add a ranking to an object. The crucial steps are to understand:

• the structure of your source table
• how the rank is being determined
• what the results table shows and to make it clear to the report viewers.

Find more articles from SAS Global Enablement and Learning here.