topic proc tabulate - order output by PCTSUM in SAS Programming

proc tabulate - order output by PCTSUM

Ronein — Mon, 04 Feb 2019 06:58:07 GMT

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;

Re: proc tabulate - order output by PCTSUM

andreas_lds — Mon, 04 Feb 2019 11:19:15 GMT

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.

Re: proc tabulate - order output by PCTSUM

mkeintz — Mon, 04 Feb 2019 16:27:21 GMT

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

Run proc summary to do the aggregation work, and write the totals to data set NEED
Sort NEED by sum of invoices (or descending sum of invoices)
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;

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.
The first proc generates the INVTOTAL for sorting, and the MEAN=INVOICE to use as an analysis variable after sorting.
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.

Re: proc tabulate - order output by PCTSUM

ballardw — Mon, 04 Feb 2019 16:10:24 GMT

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.