topic Re: Proc Tabulate Sorting by All (Total) column in SAS Procedures

Proc Tabulate Sorting by All (Total) column

rexc16 — Mon, 23 May 2016 16:24:33 GMT

I am trying to sort my proc tabulate by the ALL value. I have searched high and low for a solution and am starting to believe it is not possible!

proc tabulate data = trigs MISSING ORDER=INTERNAL out=triggers;

class FileDt;

class LOB;

class TRIG;

var counts;

table (LOB='') * (TRIG=''), (FileDT='' ALL) * (Counts='' *SUM=''*F=Comma12.)/row=float box='by Trigger';

keyLabel ALL = 'Total';

run;

I am trying to sort by the value created here - (FileDT='' ALL)

Re: Proc Tabulate Sorting by All (Total) column

ballardw — Mon, 23 May 2016 17:22:16 GMT

It may help to provide some output that your are currently getting and the result you want.

"ALL" is not sortable as it is a positional category, after all values of FileDt in this case.

If you want the total line to appear first then use (All FileDt).

If you want to sort by the value of COUNT statistics that is whole other kettle of fish.

Re: Proc Tabulate Sorting by All (Total) column

rexc16 — Mon, 23 May 2016 18:03:13 GMT

I want to be able to sort by the hightlighted column in the attachment.

Re: Proc Tabulate Sorting by All (Total) column

ballardw — Mon, 23 May 2016 20:27:25 GMT

The VALUE in your highlighted column is a statistic: The SUM of the variable count. Proc Tabulate doesn't support sorting by statistics directly. One approach might be to create an output data set from tabulate using the out= option, sort the result and use the order=data option but you'll have new variables to play with.

If the data you are attempting to display come from summarizing another data set you might be better off going back to the previous dataset and doing something like:

proc tabulate data = trigs MISSING out=triggers;
   class FileDt;
   class LOB;
   class TRIG /order=freq;
   var counts;
   table (LOB='') * (TRIG='' all), 
         (FileDT='' ALL)*n*f=comma6/row=float box='by Trigger';
   keyLabel ALL = 'Total';
run;

Or possibly order=freq descending if you want high to low.