Sorting by Analysis Variable in Summary Table

[RPYee]

A complete novice, I am using SAS Enterprise Guide and have created a summary table.  For ease of discussion, I have two metrics... the Classification variables are product names and the analysis variables are the number of each product on hand.  The results show the table sorted by product name with their associated inventory count.  I can only figure out how to sort this summary table by the product name, either ascending or descending.  I need this sorted by the inventory count, descending. 

Can someone PLEASE help me??

Thanks, in advance!

Rita Yee

Project Engineer, FedEx Express

[Reeza]

Are you using the GUI or code?

[RPYee]

GUI.  I'll try to remember to add that bit of info in the future.  I don't even know how to add code 😕  

[TomKari · Accepted solution]

Bad news and good news...

I don't believe this is an option in PROC TABULATE, which is the underlying procedure used by "Summary Tables".

However, there is a fairly easy option, which I believe you can implement completely using the GUI interface.

First, do the needed summarization using the "Summary Statistics" task. On the "Results" tab, unclick "Show Statistics", and click "Save Statistics to data set". Set up the "Statistics" tab to give you the results you want.

After you run the task, you should see a dataset with your product quantities summarized by product name. Now use the "Sort Data" task to sort the dataset by descending quantity.

Now that you have the summarized dataset you want, you can basically use "Summary Tables" as a formatting engine. Run it, and for the "Classification Variables" submenu, pick "sort by" Data set order. Make your statistic a sum (really, you're just printing the existing sum as there's only one record per product name), and away you go!

I've been using this one for years...I love the formatting from PROC TABULATE, but I can't always get it to summarize exactly what I want.

Tom

[RPYee]

Although this solution does work, I'm so disappointed in how convoluted it is to perform a simple sort.  I guess I've been spoiled. 😞

[TomKari]

In my opinion, it always comes down to tradeoffs.

On the one hand, I agree this is a little more complex than would be necessary in a totally data-driven tool like Excel. And note that a one-step solution with PROC TABULATE does work for many of the use cases, just not yours.

On the other hand, i) this solution will work for any data volume...I'm accustomed to working with record counts of 10 and 20 million, where Excel becomes a bit impractical! And, the nature of this solution is such that it can be "operationalized" and turned into a repeating solution that can be run against different datasets as needed, with no manual intervention.

My two cents worth,

   Tom

[RPYee]

Thanks for your two cents 🙂

I don’t know why you assumed I’m using Excel.  I do, but not for things of this nature.  My experience has been with Hyperion, previously Brio, that was bought by Oracle.  They’re killing support of Hyperion so we are having to convert everything to a different package.  SAS was selected for a multitude of reasons, but even the SAS contractors we’ve hired to help move jobs over have been highly impressed with Hyperion.  SAS still feels clunky to me this early on.  I can only pray that continued exposure will improve my confidence and opinion of it.  I fought Hyperion early on, too, being a creature of habit.

I do understand the "tradeoff" concept.  I told my boss that trading in Hyperion for SAS felt like him trading his 350Z for a shiny new Pinto.   All kidding aside, there are a couple aspects of SAS I do really like.  I just hope to finally gain some confidence in the entire process.  At this point I'm only using the GUI, having little to no exposure to actual background programming.  I don't know if that would even help. 

Have a blessed day!!

Rita

[Reeza]

@RPYee If you programmed, yes there are ways to accomplish this within a single step. The GUI gets you 90% of the way there. That last 10% is sometimes a pain....

Does the following do what you want, it in the Summary Tables Task, there's a side menu that shows the Class values. Set it to Descending and see if the output is what you want. I'm using SAS 9.4 with EG 7.1.

[RPYee]

Nope.  This just sorts the titles alphabetically in either direction, not their values.

Thanks though 🙂

[Reeza]

Post a screen shot of your table structure if you want me to look into this further, but it's fine to drop it as well.

In my test, the data sorted correctly, but it's possible with a more complicated structure it doesn't. 

Cheers.