sorting proc report by the values in the individual across columns

[js5]

Dear community,

I am trying to sort the sashelp.cars dataset by the values in the individuals type groups:

proc report data=sashelp.cars;
	columns make type, horsepower;
	define make /group;
	define type /across;
	define horsepower /max;

So the final output should sort by Wagon BHP, then by Truck BHP, etc. Is this possible without having to transpose and pre-sort the dataset? Thank you for your help in advance!

[unison]

If you can't do it with order= option such as

define type /across order=data;

Then it's far less trouble to just pre-sort the data in the way you want the across columns to appear.

[ed_sas_member]

Hi @js5 

I would add 'descending' to the DEFINE statement:

	define type /across descending;

In fact, order = data sorts the categories in the order that the variable values are encountered in the data set.

The default order, which is 'formatted' sort the data by the variable’s formatted values.

in your case, I understand that you want an alphabetical descending order.

Best,

[js5]

Thank you both for your responses! Unfortunately, all the options do is change the order of the type columns. What I need is to sort the make by the max BHP values in the specific colums:

1. first by max SUV BHP
2. then by max Sedan BHP
3. then by max Sports BHP

etc. Is this possible with proc report alone, or would I need to transpose the data first?

[Cynthia_sas]

Hi:
You cannot order the ACROSS items by the value of the statistic. In your program, you are asking PROC REPORT to calculate the MAX of horsepower, so PROC REPORT will not calculate the MAX and then also order by the calculated max. In this instance, you would need to make 2 passes through the data -- one to calculate the max horsepower for each type and then you'd sort by MAX horsepower within type. Then the next (probably using PROC REPORT) to display the already calculated max values and here's where ORDER=DATA will come in handy if you've already calculated the max and ordered.

Cynthia