proc tabulate - order output by PCTSUM

[Ronein]

Hello

In this proc tabulate table I want to order  by column PCTSUM

The code is not working.

How can I order output by PCTSUM?

proc tabulate data=sashelp.cars;  
class Make/ORDER=PCTSUM;  
var Invoice;  
table Make='',Invoice=''*(n='Cars sold' PCTN='%Cars sold' SUM='Sum sales'  PCTSUM='PCT SUM sales')/box='Brand';
run; 

[andreas_lds]

Have a look at the documentation: the option "order" is defined as

ORDER=DATA | FORMATTED | FREQ | UNFORMATTED

So there is no way to specify pctsum as sort-order.

[mkeintz · Accepted solution]

You can't order by PCTSUM, but as @andreas_lds  notes you  can order by data (i.e. in order of the data  set).  So you could

1. Run proc summary to do the aggregation work, and write the totals to data set NEED
2. Sort NEED by sum of invoices (or descending sum of invoices)
3. Run proc tabulate against data set need, using order=data.

proc summary data=sashelp.cars;
  class make;
  var invoice;
  output out=need (where=(_type_=1)) mean=invoice sum=invtotal;
run;
proc sort;by descending invtotal;run;

proc tabulate data=need  order=data;
  class make;
  var invoice;
  freq _freq_;
  table Make='',Invoice=''*(n='Cars sold' PCTN='%Cars sold' SUM='Sum sales'  PCTSUM='PCT SUM sales')/box='Brand';
run;

1. The "where = (_type_=1)" option in the proc summary tells sas to only output the 1-way frequencies and sums.  Otherwise it would also include a single output record with total frequency and overall sum (_TYPE_=0). 
   edited addition:  proc summary also creates the automatic variables _FREQ_ which give number of observations contributing to the aggregate record.
2. The first proc generates the INVTOTAL for sorting, and the MEAN=INVOICE to use as an analysis variable after sorting.
3. The second proc tabulate uses the "order=data" option.  The statement "FREQ _FREQ_;"  tells sas to treat the observations in data set need as weighted observations.
   edited addition:  _FREQ_ is the automatic variable created in step 1.

[ballardw]

Or in a modification of @mkeintz use one pass through proc tabulate to create an output data set then a data step for some modification and then sort. You may use proc tabulate but with pre-calculated statistics you need to be careful not to ask for pcts or sums again. Or use proc report list output.