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

[runningjay]

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.

[RW9]

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.

[runningjay]

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

[snoopy369 · Accepted solution]

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.