I use proc tabulate to create an HTML report:
PROC TABULATE data=data ; title "Title"; class month currency product
table (Month (all='Sum_year'))* (Currency (all='Sum'))* (Product (all='Sum')), Amount;
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?
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.
ods listing close;
ods html file='c:\temp\output\mydata_table.html' style=sasweb;
PROC TABULATE data=mydata ;
class month currency product ;
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')
amount / box='Many Different Tables in the Row Dimension';