proc tabulate to create multiple by tables -how to control order of tables

[Ronein]

Hello

I am using proc tabulate to create multiple summary tables (For each category of  variable "origin" I create a separate summary table).

I would like to modify the code in order to create few requests:

1-I want that the order of the tables will be by the 'Total' of each table (So the order of the tables should be  'Europe'  , 'USA' ,'Asia')

2-I want to export the tables to one excel file and put the tables side by side of each other

3-For each table I want to have a title with origin information with yellow background

4-I want to display the values of  invoice sum by millions of $ 

proc sort data=sashelp.cars out=cars;
by origin;
run;

proc tabulate data=cars;
class make;
by origin;
var invoice;
table make ALL='TOTAL'  ,invoice=SUM;
Run;

thanks for your advice

[ballardw]

Proc Tabulate is going to need some help from you to create custom orders.

Either you have to sort the data prior to the procedure and use the ORDER=DATA option or

1) create a new numeric variable for the order you want something to appear,

2) create a format to show that numeric variable with the desired text

3) use order= unformatted

OR you may be able to create a custom format for the class variable(s) in the desired order and the PRELOADFMT option. which may have other issues.

Showing a statistic in millions will require 1) a custom format, there is an example in Proc Format documentation and you apply a format by something like:

      invoice=''*sum='Sum'*f=formatname. 

if you miss the *f= before the format name you will get syntax errors.

Depending on the specific actual data some of this may be accomplished by pre_summarizing the data with Proc summary as you can get "totals" of the class variables in combination and use _type_ to control the ones you want for a table.

Not even going to touch the Excel side by side

The option I showed yesterday using ORIGIN as a CLASS variable and in a Page expression will place a "title" before each table that looks like "Origin <origin value>" . By providing a more interesting label to the variable as an override you can get a custom "title"

proc tabulate data=sashelp.cars;
class make;
class origin;
var invoice;
table origin=[Label="Area of origin is: " style=[background=yellow] ],
      make ALL='TOTAL'  ,
      invoice=''*sum='Sum';
Run;