proc tabulate-Control columns order

[Ronein]

Hello

I am running proc tabulate to create a summary report.

I want to ask how can I control the order of the columns in the summary report.(for example: By total sales)

I want that the Region with highest total sales will appear first.

So we need to see following order of columns:

Middle East   5,631,779

United States   5,503,986

Western Europe  4,873,000

Canada  4,255,712

Central America/Caribbean 3,657,753

South America  2,434,783

Eastern Europe  2,394,940

Africa   2,342,588

Pacific   2,296,794

Asia   460,231

proc tabulate data=sashelp.shoes f=comma18.;
  var sales inventory returns;
  class region;
  table sales*sum  inventory*mean  returns*max, region all;
run;

[FreelanceReinh]

Hello @Ronein,

Good question. It seems that, in general, without additional preparations all of the available settings of the ORDER= option of the CLASS statement establish different column orders.

So, I think you'd need to make "additional preparations," for example create a format with the notsorted option, based on the properly sorted output dataset of a preliminary PROC TABULATE step, and use this format for the CLASS variable in question (REGION in your example) together with the options preloadfmt order=data of the CLASS statement in the final PROC TABULATE step, as shown below.

ods select none;
proc tabulate data=sashelp.shoes out=_tmp(drop=_:);
var sales;
class region;
table sales*sum, region;
run;
ods select all;

proc sort data=_tmp;
by descending sales_sum;
run;

data _fmt;
retain fmtname '$_tmpfmt' hlo 'S';
set _tmp(rename=(region=start));
label=start;
run;

proc format cntlin=_fmt;
run;

proc tabulate data=sashelp.shoes f=comma18.;
var sales inventory returns;
format region $_tmpfmt.;
class region / preloadfmt order=data;
table sales*sum inventory*mean returns*max, region all;
run;

Note that in spite of "order=data" there is no need to sort or modify the input dataset in any way.