topic Proc Tabulate: Summary of two different rows in SAS Procedures

Proc Tabulate: Summary of two different rows

meatmuffin — Wed, 06 Feb 2013 15:03:11 GMT

Hello,

I'm trying to do a table with proc tabulate where I'm calculating different totals.

At the moment it looks like the following:

With the following code:

PROC TABULATE
DATA=my.data;

VAR SUM_A_KOST_J SUM_A_HB SUM_A_TB SUM_AUSGABEN_OEFFENTLICH;
CLASS PERH_J / ORDER=UNFORMATTED MISSING;
CLASS KAT1AG / ORDER=UNFORMATTED MISSING;

TABLE /* Zeilendimension */
PERH_J,
/* Spaltendimension */

(ALL='SUBTOTAL_1')*SUM_AUSGABEN_OEFFENTLICH
KAT1AG*SUM_AUSGABEN_OEFFENTLICH
(ALL='SUBTOTAL_2')*SUM_A_KOST_J
KAT1AG*SUM_A_KOST_J
;
;
RUN;

Now I would like to add a row which calculates me the TOTAL of SUBTOTAL_1 and SUBTOTAL_2.

Is this possible? And how can I do this?

Thanks for your help.

Re: Proc Tabulate: Summary of two different rows

ballardw — Wed, 06 Feb 2013 15:44:52 GMT

If you change Perh_J, to

Perh_J All='Total'

You will get the sum of 2011 and 2012 for all columns. If want only Subtotal21 and Subtotal_2 summed then you're likely outside of proc tabulate.

Re: Proc Tabulate: Summary of two different rows

Astounding — Wed, 06 Feb 2013 15:58:41 GMT

One of the principles of using PROC TABULATE is that any one cell in the report can be based on just a single analysis variable. So the answer is no, you can't do that using your current structure to the data. However, if you were to preprocess your data and create a new analysis variable then it would be possible:

newvar = sum_ausgaben_offentlich + sum_a_kost_j;

Then add NEWVAR to the VAR statement, and the rest would be fairly easy.

Good luck.

Re: Proc Tabulate: Summary of two different rows

meatmuffin — Fri, 08 Feb 2013 14:35:36 GMT

Thank you very much for the answers.

Unfortunately the structure of the data isn't so easy as it looks like while creating the proc tabulate.

Well, I have to search for a possible preprocess.

Re: Proc Tabulate: Summary of two different rows

Astounding — Fri, 08 Feb 2013 17:34:17 GMT

In that case, here's a similar approach that might work. Instead of printing the report with PROC TABULATE, create an output data set instead. Then modify the output data set and use that as the input to a second PROC TABULATE to actually print the report.