Desktop productivity for business analysts and programmers

How do I choose the order values appear as either rows or column headers in a SAS EG Summary Table

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How do I choose the order values appear as either rows or column headers in a SAS EG Summary Table

I'm using SAS EG Version 7.13. I have a field that contains nine values. When creating a summary table and using either the Summary Tables wizard or the Summary Tables function the order the values appear in rows or columns is always alphabetical. How can I select the order the values appear as either rows or columns instead of the alphabetical selection or the value with the largest value?

 

For example if my field is FOOD and my values are Apple, Banana, Carrot, Eggplant and Mushroom I want the row order (or column order) to appear as the following:

 

Mushroom

Carrot

Banana

Apple

Eggplant

 

Thanks for any guidance on how to do this in SAS EG Summary Tables wizard or Summary Tables function.


Accepted Solutions
Solution
‎02-08-2018 04:21 PM
Super Contributor
Posts: 320

Re: How do I choose the order values appear as either rows or column headers in a SAS EG Summary Tab

Posted in reply to runningjay

If your dataset is already sorted in the order you want (Mushroom, Carrot, etc.), then:

 

1.  Run Summary Tables (not wizard)

2.  Add your classification variable

3.  Under the options for that classification variable once added is "Sort by"; you can choose "Data Set Order"

 

Then it will come out in the order that the classification variable is encountered in the dataset.

 

If that is not possible (if you don't have it sorted that way), you can do it with a custom format, but it's not doable directly in the task.

 

For reference, you have four options: Formatted, Unformatted, Dataset Order, or Descending, in that particular task. 

View solution in original post


All Replies
Super User
Super User
Posts: 9,193

Re: How do I choose the order values appear as either rows or column headers in a SAS EG Summary Tab

Posted in reply to runningjay

Use formats.  So set a format to something like:

 

proc format;
  value fruit
    1="Mushroom" 
    2="Carrot"
...; run;

Then convert your data to be 1 2 3 etc. and apply the format to it:

 

 

data want;
  set have;
  select(fruit);
    when ("Mushroom") fruit_code=1;
    when ("Carrot") fruit_code=2;
    ...;
  end;
  format fruit_code fruit.;
run;

This way the text will be displayed, but the underlying sort would use the unformatted number.  As for "SAS EG Summary Tables wizard or Summary Tables function" - no idea, I only work in code as its most flexible.

 

New Contributor
Posts: 2

Re: How do I choose the order values appear as either rows or column headers in a SAS EG Summary Tab

Thanks for the info. I was looking for a way to do this within SAS EG's wizards, but this is helpful.

Solution
‎02-08-2018 04:21 PM
Super Contributor
Posts: 320

Re: How do I choose the order values appear as either rows or column headers in a SAS EG Summary Tab

Posted in reply to runningjay

If your dataset is already sorted in the order you want (Mushroom, Carrot, etc.), then:

 

1.  Run Summary Tables (not wizard)

2.  Add your classification variable

3.  Under the options for that classification variable once added is "Sort by"; you can choose "Data Set Order"

 

Then it will come out in the order that the classification variable is encountered in the dataset.

 

If that is not possible (if you don't have it sorted that way), you can do it with a custom format, but it's not doable directly in the task.

 

For reference, you have four options: Formatted, Unformatted, Dataset Order, or Descending, in that particular task. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 193 views
  • 1 like
  • 3 in conversation