turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Need help in Proc Tabulate

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-04-2007 03:51 PM

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,

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,

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-04-2007 05:28 PM

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

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

year*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

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

[/pre]

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

cynthia

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-05-2007 09:48 AM

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,

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,

;

;

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 "

But not working? How to get this working.

thanks,

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-05-2007 11:52 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

09-05-2007 01:04 PM

Cyndy,

That helped me.

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

(year all)*(am fee)

thanks,

That helped me.

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

(year all)*(am fee)

thanks,