Proc Tabulate - Order of Columns

[steve_citi]

I would like to order my columns by the column totals, in descending order. Unfortunately it appears that the options available on the CLASS statement will either order your columns by the column headings(alphabetically), or by the frequency of the data (order=freq) but not by the actual total value.

I have tried order=formatted, but it did not appear to work. I am unsure if the "formatted" refers to the data or to the heading.

Can anoyone provide a solution to order the columns in descending order by the column totals?

[Cynthia_sas]

Hi:
TABULATE will only put the ORDER on the CLASS statement. Normally, TABULATE wants a variable to be used for one thing -- setting categories or calculating statistics. What you want asks for a single variable to have 2 roles -- calculate the sum (use the variable for analysis) but then ALSO order by the calculated sum (use the variable as a CLASS variable).

One approach is to make one pass through the data and calculate the sum for ordering, and then to use that calculated sum for sorting purposes. In that case, ORDER=DATA on the CLASS statement should work.

There is another approach with PROC REPORT shown in this paper:

cynthia

[pre]
** One approach with PROC SQL and TABULATE;
** First query creates the sum of sales for just 3 regions.;
** Second query creates dataset for TABULATE.;
** (probably could have done this in one query with a remerge, but wanted to keep this simple);

proc sql;
create table work.sumsale as
select distinct region, sum(sales) as sumsale
from sashelp.shoes
where region in ('Asia', 'Pacific', 'Canada')
group by region;

create table work.ordsale as
select b.sumsale, a.region, a.subsidiary, a.product,
a.sales, a.inventory, a.returns
from sashelp.shoes as a,
work.sumsale as b
where a.region = b.region
order by sumsale descending;
quit;

ods listing close;
ods html file='c:\temp\showord.html' style=sasweb;
proc tabulate data=work.ordsale f=comma14.2;
class region /order=data;
class product;
var sales;
table region all,
product*sales=' '*sum all*sales=' '*(sum='Sales' pctsum*f=6.2);
keylabel sum = ' ';
run;
ods html close;
[/pre]