Proc tabulate question

Alex1 — Sun, 06 Feb 2011 21:01:00 GMT

Hello

Please help me with the following. Let's say that I have the following table:
http://img96.imageshack.us/img96/3591/datay.jpg

I use proc tabulate to create an HTML report:
PROC TABULATE data=data ; title "Title"; class month currency product
var Amount;
table (Month (all='Sum_year'))* (Currency (all='Sum'))* (Product (all='Sum')), Amount;
RUN;

I get the following output table
http://img4.imageshack.us/img4/7752/output1.png

As you can see, the "USD" only appears in some months. Is there a way not to print the "sum", where there are no "USD" lines in data-table (in example - month 2).
Is there a way not to print the products in the total sum?

To make it easier to understand, I have uploaded a picture of how I want my output to look like:
http://img232.imageshack.us/img232/7825/output2.png

What do I have to write in Proc tabulate-statement?

Thank you!

Re: Proc tabulate question

Cynthia_sas — Mon, 07 Feb 2011 16:27:56 GMT

Hi:
As soon as you put
* (Currency (all='Sum'))*
into the ROW dimension crossed with MONTH and PRODUCT, you are going to get that summary (ALL) for Currency --
whether you have 1 currency or 10 or 100 currency values in the table. The way TABULATE works is that the table rows for CURRENCY are
placed in the row dimension, then you have a space or blank operator and then the ALL -- that means after the CURRENCY rows have been written,
the ALL row(s) will be written -- the ALL has no visibility of how many values there were for CURRENCY.

Possibly reworking your ROW dimension might help you figure out a slightly different approach. Here are two different examples in the code below --
note that there are different TABLE statements so you'll have to scroll down to see the results. The BOX= option was used to provide a name
for each table.

And, here are some papers about PROC TABULATE that may help you understand the ALL usage and the space/blank and * usage a bit better.
http://www2.sas.com/proceedings/sugi27/p060-27.pdf
http://www2.sas.com/proceedings/sugi30/243-30.pdf
http://www2.sas.com/proceedings/sugi30/258-30.pdf

cynthia
[pre]
data mydata;
infile datalines dlm=',' dsd;
input month currency $ product amount;
return;
datalines;
1, EUR, 1, 100
1, USD, 2, 200
1, USD, 1, 100
1, EUR, 3, 300
1, EUR, 4, 400
2, EUR, 1, 300
2, EUR, 4, 150
2, EUR, 3, 150
2, EUR, 2, 400
2, EUR, 1, 500
2, EUR, 2, 100
2, EUR, 1, 300
3, USD, 4, 100
3, USD, 3, 200
3, EUR, 2, 300
3, EUR, 1, 400
3, USD, 2, 100
3, USD, 3, 100
3, EUR, 4, 200
3, USD, 1, 300
;
run;

ods listing close;
ods html file='c:\temp\output\mydata_table.html' style=sasweb;
PROC TABULATE data=mydata ;
title "Title";
class month currency product ;
var Amount;
table (Month (all='Yearly Total')) *currency *(Product (all='Product Total')) all='Currency Total All Products'*currency all='Yearly Total All Products',
amount /box='Changed Table';

table month all='Total Month' currency all='Total currency' product all='Total Product'
(month*currency all='Currency and Month Totals')
(month*currency*product all='Currency, Month and Product Totals')
all='Currency Totals'*currency
all='Product totals'*product,
amount / box='Many Different Tables in the Row Dimension';

table (Month (all='Sum_year'))* (Currency (all='Sum'))* (Product (all='Sum')),
Amount / box='Original Table';

RUN;
ods html close;
[/pre]

topic Re: Proc tabulate question in SAS Procedures

Proc tabulate question

Re: Proc tabulate question