topic Re: PROC Tabulate - Sum in ODS and Base Reporting

PROC Tabulate - Sum

deleted_user — Fri, 05 Jun 2009 16:08:35 GMT

I have the following code for proc tabulate:

ods tagsets.ExcelXP style=sasweb2 file='c:\PC_SAS\Balance_Generation_Final.xls'
options( default_column_width='12, 12, 12, 12, 12'
sheet_interval='none' sheet_name='Total Summary' frozen_rowheaders='yes' rules='yes');
proc tabulate data=bal_gen_calc order=data;
where _type_ in (0,29); title;
class campaign_type term rate dte;
classlev Dte;
var tran_amt apr offer_term;
table Campaign_Type,
term *rate *(tran_amt*f=dollar15. apr*f=percent10.2 offer_term)* SUM='',
Dte / box={label=_PAGE_ s=[background=white]};
label tran_amt='Balances';
run;quit;

I would like to see the grand totals at the bottom of the table for tran_amt,apr,offer_term. Any ideas on how to do that? APR is a percentage.

Re: PROC Tabulate - Sum

Peter_C — Fri, 05 Jun 2009 19:03:44 GMT

please could you provide a small sample file like the file
data=bal_gen_calc

Re: PROC Tabulate - Sum

Cynthia_sas — Fri, 05 Jun 2009 21:30:16 GMT

Hi,
Generally, grand totals in PROC TABULATE come through the use of the ALL universal class variable. So I might expect to see something like this in your TABLE statement:
[pre]
table Campaign_Type,
term *rate *(tran_amt*f=dollar15. apr*f=percent10.2 offer_term)* SUM='' all*(tran_amt*f=dollar15. apr*f=percent10.2 offer_term)* SUM='',
Dte all
/ box={label=_PAGE_ s=[background=white]};

[/pre]

You could even have Campaign_Type ALL, in your TABLE statement, but you said you wanted the grand totals at the bottom of each table, not at the bottom of each page dimension.

cynthia

Re: PROC Tabulate - Sum

deleted_user — Mon, 08 Jun 2009 15:15:29 GMT

That worked! Thank you.

Once I add the totals to the bottom, how do I specify which I want summed verses a just showing a percentage?

For example: The tran_amt needs to be summed, but the APR and offer_term do not(they are percentages). The all step I am using now sums all 3 which leaves me with large % totals.

Any way to not have all 3 sum?

Re: PROC Tabulate - Sum

Cynthia_sas — Mon, 08 Jun 2009 16:57:03 GMT

Hi:
Take apr and offer_term out of the all*() parentheses and just leave tran_amt inside the parentheses.

cynthia