Proc Tabulate: Summary of two different rows

[meatmuffin]

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.

[ballardw]

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.

[Astounding · Accepted solution]

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.

[meatmuffin]

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.

[Astounding]

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.