topic Re: Need help in Proc Tabulate in ODS and Base Reporting

Need help in Proc Tabulate

deleted_user — Tue, 04 Sep 2007 19:51:54 GMT

Hi,
Using Proc Tabulate step, I am able to display the three variables listings:

Say Row 1 that had some values in year1, year 2 etc

Proc tabulate which I am using now displays Totals to the end for year 1, year 2 etc. I need the total for the rows across totalling in all the years. For Ex:

Year1 Year2 Year3 Total
Row1 20 30 50 100

How to get this Total shown to the end in Row1?

thanks,

Re: Need help in Proc Tabulate

Cynthia_sas — Tue, 04 Sep 2007 21:28:32 GMT

Hi:
The universal CLASS variable ALL is what you want to use. The placement of ALL on the TABLE statement (in the row dimension or the column dimension or in both dimensions) will determine where the summary line (the ALL line) appears.

Consider this example code (submit the code and review the results from each separate PROC TABULATE step):
[pre]
ods html file='c:\temp\tab_all.html' style=sasweb;

proc tabulate data=sashelp.prdsale f=comma12.;
title 'Using All Both Dimensions';
class year prodtype;
var actual;
table prodtype all,
year*actual*sum all*actual*sum;
keylabel sum=' '
all='Total';
run;

proc tabulate data=sashelp.prdsale f=comma12.;
title 'Using All Row Dimension Only';
class year prodtype;
var actual;
table prodtype all,
year*actual*sum;
keylabel sum=' '
all='Total';
run;

proc tabulate data=sashelp.prdsale f=comma12.;
title 'Using All Column Dimension Only';
class year prodtype;
var actual;
table prodtype,
year*actual*sum all*actual*sum;
keylabel sum=' '
all='Total';
run;

ods html close;

[/pre]

This first TABLE statement has the ALL in both dimensions, row and column:
[pre]
table prodtype all,
year*actual*sum all*actual*sum;
[/pre]
PRODTYPE will go down the rows and the TOTAL of ALL PRODTYPES will be the last row. Then the SUM of the ACTUAL variable values will be under each YEAR in the column dimension. Finally, the SUM of all the YEAR values for ACTUAL (all*actual*sum) will be the last column to the right of the table.

Then for a TOTAL in just the ROW dimension (at the bottom of all the rows), I would have only:
[pre]
table prodtype all,
year*actual*sum;
[/pre]

And for a TOTAL in just the COLUMN dimension (to the right of all the years), I would have only:
[pre]
table prodtype,
year*actual*sum all*actual*sum;
[/pre]

For more help using ALL with PROC TABULATE, you might consider contacting Tech Support.

cynthia

Re: Need help in Proc Tabulate

deleted_user — Wed, 05 Sep 2007 13:48:01 GMT

Thanks Cynthia,
That worked exactly what I needed.

But I have two variables in VAR list. I was trying two different ways. But it throws error: " There are multiple analysis variables associated with a single table cell in the following nesting "

proc tabulate data=all;
class NB YEAR ;
VAR AM FEE
;
table NB all,
Year*am*sum all*am*sum,
Year*fee*sum all*fee*sum
;
;

KEYLABEL ALL = 'TOTAL'
SUM = ' '
;

run;

In the above Tabulate Procedure, highlighted one in bold for the fee is not working. I tried giving the 'Fee' variable in the same " Year*am*sum all*am*sum "

But not working? How to get this working.

thanks,

Re: Need help in Proc Tabulate

Cynthia_sas — Wed, 05 Sep 2007 15:52:17 GMT

Hi:
I'm in a class today, so really can't take a close look. Just a couple of idea. The comma (,) is not just punctuation. Right now, you are creating a 3 dimensional table -- one with page, row and column dimensions:
[pre]
table NB all, <---page
Year*am*sum all*am*sum, <--- row
Year*fee*sum all*fee*sum <---col
;
[/pre]

I suspect that you are getting PROC TABULATE error messages that say something like that there are multiple analysis variables associated with a table cell and then you probably see a bunch of variables and asterisks listed.

Every place that you have a comma is an instruction to PROC TABULATE to go to a new dimension. If this is what you intend (a 3-dimensional report) -- then you'll need to move all your statistics to just 1 dimension or the other (move all the SUM into either the ROW or COL dimension).

Otherwise, if this is NOT what you intend, you might consider reading the documentation about table operators or contacting Tech Support for more help.

cynthia

Re: Need help in Proc Tabulate

deleted_user — Wed, 05 Sep 2007 17:04:47 GMT

Cyndy,
That helped me.

I replaces the earlier second variable in "Table" with below and report looks good.

(year all)*(am fee)

thanks,