Desktop productivity for business analysts and programmers

Sorting in Summary Tables

Not applicable
Posts: 0

Sorting in Summary Tables

I created a summary table, with numerous classification variable on the left hand side of the table. Additionally, I have numerous analysis variables as well on the top. SAS currently sorts or lists the table by order of the classification variable. I would like to sort the the table by one of the analysis variable (descending order). Any ideas as to how to do this.

Not applicable
Posts: 0

Re: Sorting in Summary Tables

Posted in reply to deleted_user
I have a nagging feeling that I may be missing something here but why not just use PROC SORT. It will sort any dataset by any variable in either order. If that wasn't what you were looking for perhaps you could elaborate.

Jim Lane
Posts: 9,431

Re: Sorting in Summary Tables

Posted in reply to deleted_user
It's also possible that the task being used to generate the table has an ORDER= option embedded in the code and that ORDER= option needs to change. Looking at the code generated from the task should clarify what's going on.

In some instances, doing a SORT -before- the task can work with the ORDER= option -- it really depends on the procedure being used and the desired outcome.

If I'm understanding the question, however, it sounds like there are class variables going down the rows and sum or count of the analysis variables going across the columns of the SUMMARY table:
clvar var1 var2 var3
aaa 100 200 300
bbb 400 200 300
ccc 101 100 99

Something like the summary table above. The problem I see with showing the analysis variables in DESCENDING order going across the columns, is that for row 'aaa' the VAR3 value would be in the first column, but for row 'bbb', VAR1 value would be in the first column and for row 'ccc', the VAR1 column would be the first one, etc, etc. Every row's order in the summary table would be based on different variables. Unless you have very regular data, it seems like this is the most likely case.

Most SAS procedures would expect to ALWAYS have the sum of the VAR1 values showing in the first column of the above report table. The variables going across the table as columns, in the above instance, represent the sum of all the VAR1 values, all the VAR2 values, all the VAR3 values -- they're not arbitrary columns, they are linked to their respective variables.

Ordering on the classification variables makes the most sense for the above table given the meaning of the variables going across the top of the table.

If you really wanted to order each row's summary values for VAR1, VAR2 and VAR3 independently of each other, you could create an output dataset from the summary table task and then manipulate that output data, so that you created NEWORD1, NEWORD2, NEWORD3 and then for every row, your program would put the highest value for the row in NEWORD1, the next highest value in NEWORD2 and the next highest value in NEWORD3, etc, etc.

I think you could probably do it with a couple of PROC TRANSPOSE steps and a sort or in Data step processing.

Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation