Order table results by mean

[Warwick Smith]

I'd like to produce a table of means by categorical variable that orders the results by mean, from highest to lowest, instead of by the alphabetical order of the categorical variable.

Example below where results are presented in alphabetical order (I know the example doesn't really make any sense because it's all means with only 1 data point but it works to illustrate the point).

I've tried the "order" option but STATA tells me it's not allowed. Just a novice with STATA's new table commands.

sysuse auto
table ( make ) (), statistic(mean headroom) nformat(%9.1f  mean)

[Joro Kolev]

I do not know how to use the new -table- command.

But what you want to do can be done with something that I call "list table":

. sysuse auto
(1978 Automobile Data)

. egen meanprice = mean(price), by(rep)

. egen tagrep = tag(rep)

. sort meanprice

. list meanprice if tagrep

     +----------+
     | meanpr~e |
     |----------|
  1. |   4564.5 |
  4. |     5913 |
 21. | 5967.625 |
 27. |   6071.5 |
 44. | 6429.233 |
     +----------+

[Joro Kolev]

If you want from highest to lowest:

. sysuse auto, clear
(1978 Automobile Data)

. egen meanprice = mean(price), by(rep)

. egen tagrep = tag(rep)

. gsort -meanprice

. list meanprice if tagrep

     +----------+
     | meanpr~e |
     |----------|
 12. | 6429.233 |
 52. |   6071.5 |
 61. | 5967.625 |
 67. |     5913 |
 73. |   4564.5 |
     +----------+

[Carlo Lazzaro]

As Warwick asked for a table reporting -mean- in descendig order, with a shameless exploiting of Joro's helpful code:

sysuse auto

. egen meanprice = mean(price), by(rep)

. egen tagrep = tag(rep)

. gsort -meanprice

. list meanprice if tagrep

     +----------+
     | meanpr~e |
     |----------|
 12. | 6429.233 |
 52. |   6071.5 |
 61. | 5967.625 |
 67. |     5913 |
 73. |   4564.5 |
     +----------+

.

PS: The code-burgler obtained what he deserved!

[Nick Cox]

While there is likely to be a smart way to do this in table in 17, which I have yet to get to grips with, here is a work-around that may serve.

See myaxis from SSC. https://www.statalist.org/forums/for...e-or-graph-use

This works for me with myaxis installed.

sysuse auto

table ( make ) (), statistic(mean headroom) nformat(%9.1f  mean)

myaxis axis=make, sort(mean headroom)

table ( axis ) (), statistic(mean headroom) nformat(%9.1f  mean)

[Warwick Smith]

Thank you both (Carlo and Joro). Your code works but I was really hoping to make use of the new table command in STATA 17, which is capable of creating great tables with a lot of flexibility (except sorting results perhaps). I don't see a way to combine what you've done with the table command from S 17.

The result you achieved can also be done with a combination of "collapse" and the "extremes" command from the package with the same name. That said, I hadn't used tag before and it's really useful.

Thanks Nick, just editing this as I posted it before I had seen your response. I will try that now.

[Warwick Smith]

Thanks Nick, that worked a treat. Picked up a couple of new things here - thanks to all three of you.

[Joro Kolev]

For what I understand, the "lot of flexibility" of the new -table-command does not include the flexibility that you are looking for , or else you would not be asking the question.

If you figure out how to do this in the new -table- command do report back on how it is done. To my knowledge in the old tabulations one did not have control over how the content of the table is sorted.

Thank you both (Carlo and Joro). Your code works but I was really hoping to make use of the new table command in STATA 17, which is capable of creating great tables with a lot of flexibility (except sorting results perhaps). I don't see a way to combine what you've done with the table command from S 17.

The result you achieved can also be done with a combination of "collapse" and the "extremes" command from the package with the same name. That said, I hadn't used tag before and it's really useful.

Thanks Nick, just editing this as I posted it before I had seen your response. I will try that now.