BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RPYee
Quartz | Level 8

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

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

View solution in original post

9 REPLIES 9
Reeza
Super User

Are you using the GUI or code?

RPYee
Quartz | Level 8

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

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
TomKari
Onyx | Level 15

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
Quartz | Level 8

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. 😞

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
TomKari
Onyx | Level 15

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
Quartz | Level 8

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

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Reeza
Super User

@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.

 

 

delete_sort.JPG

RPYee
Quartz | Level 8

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

Thanks though 🙂

 

___________________________________
The fact that I ask for help simply means I am inexperienced and under-educated. Please don't assume I am incompetent and uneducated.
Reeza
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3491 views
  • 2 likes
  • 3 in conversation