proc tabulate-Control columns order

Ronein — Wed, 19 Dec 2018 05:01:20 GMT

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;

Re: proc tabulate-Control columns order

FreelanceReinh — Wed, 19 Dec 2018 13:19:32 GMT

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.

topic proc tabulate-Control columns order in SAS Programming

proc tabulate-Control columns order

Re: proc tabulate-Control columns order