topic sorting proc report by the values in the individual across columns in ODS and Base Reporting

sorting proc report by the values in the individual across columns

js5 — Thu, 09 Jan 2020 14:05:14 GMT

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!

Re: sorting proc report by the values in the individual across columns

unison — Thu, 09 Jan 2020 14:20:23 GMT

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.

Re: sorting proc report by the values in the individual across columns

ed_sas_member — Thu, 09 Jan 2020 14:48:29 GMT

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,

Re: sorting proc report by the values in the individual across columns

js5 — Fri, 10 Jan 2020 10:22:05 GMT

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:

first by max SUV BHP
then by max Sedan BHP
then by max Sports BHP

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

Re: sorting proc report by the values in the individual across columns

Cynthia_sas — Fri, 10 Jan 2020 17:48:06 GMT

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