BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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;

 

 

 

1 REPLY 1
FreelanceReinh
Jade | Level 19

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1603 views
  • 0 likes
  • 2 in conversation