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;
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.