Proc tabulate customized order

[Pedrommo]

Hi!

I have a dataset with two numerical variables and 3 categorical variables. I need to sort the results of my proc tabulate in descending order based on the numeric variable I use as the analysis variable in the proc tabulate. I've tried various ways to pre-sort the dataset but I can't get any results. I'm attaching my code in case you can give me a hand. Thank you so much in advance!

PROC TABULATE

DATA=WORK.RESULTADOS_TOTAL_V3

;

VAR porc_de_variacion_PD porc_de_importancia;

CLASS model / ORDER=UNFORMATTED MISSING;

CLASS variable / ORDER=UNFORMATTED MISSING;

CLASS clasificacion / ORDER=UNFORMATTED MISSING;

TABLE 

/* PAGE Statement */

all = 'Total'

clasificacion  ,

/* ROW Statement */

model *variable 

all = 'Total'  ,

/* COLUMN Statement */

(porc_de_variacion_PD * Sum={LABEL="Suma"} porc_de_importancia * Sum={LABEL="Suma"} ) ;

;

RUN;

[JosvanderVelden]

Have you seen the thread https://communities.sas.com/t5/SAS-Programming/Proc-tabulate-order-of-rows/td-p/763131?
I'm not sure if you can apply it in your case. But maybe it provides some insight.

[Pedrommo]

Hello, the forum you mention uses a categorical variable to sort instead of the analysis variable (numeric). Thanks anyway!

[ballardw]

You can create a data set using Proc Tabulate that contains the elements of the report plus some layout information.

Use the OUT=option.

It may be possible to sort that data set in the order of the statistics plus appropriate layout information and display the results though likely NOT using proc tabulate again.

You will need to look at the _TYPE_ _PAGE_ and _TABLE_ variables that hold the layout information and use them in your sort.

Without data and an example of the expected output for that data it isn't going to be possible to provide working code.

[PaigeMiller]

The CLASS statement in PROC TABULATE has a DESCENDING option, did you try that?

[Pedrommo]

Yes, I tried that, but I need to order using the VAR statement. Thanks anyway!

[mkeintz · Accepted solution]

Your code identifies TWO analysis variables, each of whose SUM you want to display in the table.  That means it would display two columns.

But, instead of ordering the table rows based on the row classification variable (ascending or descending or formatted), I think you want to order based on descending SUM of an analysis variable. 

"descending order based on the numeric variable I use as the analysis variable in the proc tabulate"   

Do I understand correctly?  If I don't you can ignore everything below.

But if so, then I ask:

1. which of the two columns do you wish to control the row order?
2. does this mean you will accept different order of the row classifier for each page dimension?

To do this, I think you will have to do something like

1. Run PROC SUMMARY to generate the cell contents in an intermediate data set.
2. Read that data set, an create an additional ad hoc row variable based on the descending SUM of an analysis variable, for each page dimension.  
3. Run a proc report or proc tabulate from the product of step 2.

[Pedrommo]

Thank you so much! This is what I was looking for.