SAS Enterprise Guide Tip: Creating a Grouped Top N Report


Hi my name is David Ghan, and I’m an instructor
with SAS. To give you an example of the type of material we cover in our course SAS Enterprise
Guide 2 – Advanced Tasks and Querying, let me show you a simple a method that allows you to
record top ranking values within groups. Perhaps you want the top ten values, or the top five values,
or generally we can call these Top N reports. Here is one such report that contains that contains the
five highest selling product groups within each of the three order types. Catalog Sales, Internet
Sales, and Retail Sales. The original data has many more product groups than each order type
group, but none sold as well as these. The trick is to generate rank values for total sales for each
record within each group. Once we have the rank values, we can subset the top five ranking values
only. From that subset we can create report. Let me switch to a demo and show you how it’s done. I
have started a new project here in Enterprise Guide. I’m going to add the product orders data to
the project. So this contains information about total sales for different product groups. Now within each
order type I’ve got some catalog sales for different groups, and scrolling down further I’ve got internet
sales for these different product groups and at the bottom retail sales for different product groups and
the total sales for each of those product groups within each order type. So the first thing I’m going
to do with this is to rank those values for total sales and do the rankings within each order type group.
So I can click on data and then go to the rank task, and I want to rank them on the total sales such the
column I’m ranking. But I want to do that within each order type group. Then in the options here,
instead of ranking from smallest to largest, I’m gonna reverse that, so rank number one will be the
highest value for total sales within a group. I’ll go ahead and run that and here’s these rank values
that have been generated and so this value of 8 here means this value for total sales was the
eighth highest within the catalog sales group. Scrolling further down, I’ll also have the rank for
the internet sales groups, and retail sales groups. So here’s the number one ranking group was
skates. Sold five thousand dollars worth of skates. Now I’ve got the rank values, I’m gonna go ahead
and filter this data so I’m only the rows worth where the rank is five or lower. Those will the top values
within each group. So I’ll click on the filter and sort task and what I want to do is output everything so
I’m gonna select output all the data. In the filter though, I’m only gonna output the rows where that
rank is less than or equal to five. I’m also gonna sort the data for the purpose of the report that’s
gonna follow, so I’m gonna sort it first by order type and then within each order type I want to sort it by
that rank value. So I’ll go ahead and execute the filter and sort. And we see now that I’ve created an
output data set where I’ve only got the five highest ranks. One through five. And that’s within each
order type so it’s ordered by first catalog sales internet sales and then retail sales and I’ve got the
top ranking groups for total sales. Now that I’ve got this data I’m ready to create the report so I’ll click
on Describe and then use the list data task to generate the report. And I want a list of the values
for product group. And for total sales value, and I want to order than by order type. So I’m gonna
group on order and also identifying label on order type so I can create the layout that I want. And
then the titles I’m gonna specify that what I’ve got here is the top five product groups by order type.
Now go ahead and run that one. And there we go. There’s our top five report. Top five product group
sales within each order type. That concludes this tip for creating Top N reports. We teach this
method in our course SAS Enterprise Guide 2 – Advanced Tasks and Querying. Thanks for
listening.

Leave a Reply

Your email address will not be published. Required fields are marked *