topic Re: Proc tabulate customized order in SAS Enterprise Guide

Proc tabulate customized order

Pedrommo — Tue, 20 Feb 2024 10:45:47 GMT

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;

Re: Proc tabulate customized order

JosvanderVelden — Thu, 22 Feb 2024 13:46:56 GMT

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.

Re: Proc tabulate customized order

ballardw — Thu, 22 Feb 2024 15:34:25 GMT

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.

Re: Proc tabulate customized order

PaigeMiller — Thu, 22 Feb 2024 15:44:28 GMT

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

Re: Proc tabulate customized order

mkeintz — Thu, 22 Feb 2024 16:18:07 GMT

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:

which of the two columns do you wish to control the row order?
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

Run PROC SUMMARY to generate the cell contents in an intermediate data set.
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.
Run a proc report or proc tabulate from the product of step 2.

Re: Proc tabulate customized order

Pedrommo — Fri, 23 Feb 2024 10:06:57 GMT

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

Re: Proc tabulate customized order

Pedrommo — Fri, 23 Feb 2024 10:51:52 GMT

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

Re: Proc tabulate customized order

Pedrommo — Fri, 23 Feb 2024 10:53:11 GMT

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