Desktop productivity for business analysts and programmers

Subtotal in EG Proc Tabulate

Reply
N/A
Posts: 1

Subtotal in EG Proc Tabulate

Subtotal is placed in the bottom of the report when result is outputted as SAS report but placed correct when HTML or PDF is used.

I use 4.1 (4.1.0.471)

Any suggestions?
SAS Super FREQ
Posts: 8,721

Re: Subtotal in EG Proc Tabulate

Hi!
The problem you've noted is a known issue with PROC TABULATE in a "row nesting" situation -- but ONLY with the SAS Report format. The key issue, in my mind, is what you were trying to do.

IF you were just playing around with SAS Report and PROC TABULATE inside EG, (without any intention to create a stored process) then the simple solution is to "NOT DO THAT" and pick HTML as the results format in EG. SAS Report is a format that is intended for use within the SAS Business Intelligence client/server environment. It is not intended for general use outside of the BI platform.

However, IF you DO have the BI platform installed and IF you want to ultimately create a stored process to surface in Web Report Studio (or elsewhere on the BI platform) -- and IF you need nesting in the "row" level, then PROC REPORT can do that for you and the subtotals will be in the right place in EG (and elsewhere in the BI platform).
For example, this TABULATE code in an EG code node, demonstrates the problem if you ask EG to generate SAS Report format results:
[pre]
proc tabulate data=sashelp.shoes;
where region in ('Asia', 'Pacific') and
(product contains 'Dress' or
product contains 'Casual');
class region subsidiary product;
var sales;
table region*(product all='SubTotal') all,
subsidiary*sales=' ';
keylabel sum = ' '
all='Grand Tot';
run;
[/pre]

But, this alternative PROC REPORT code in an EG code node, generates a table with subtotals in the right places without the issue you noted (even with SAS Report format).
[pre]
proc report data=sashelp.shoes nowd;
where region in ('Asia', 'Pacific') and
(product contains 'Dress' or
product contains 'Casual');
column region product subsidiary,sales;
define region /group;
define product /group;
define subsidiary/across;
define sales /sum;
break after region /summarize;
rbreak after / summarize;
compute after region;
region = 'SubTotal';
endcomp;
compute after;
region = 'GrandTot';
endcomp;
run;
[/pre]
Again...the easiest thing if you are concerned with EG -only- is to NOT use SAS Report format. If you are on the BI platform and if you are generating tasks to be turned into a stored process, then the PROC REPORT workaround may be an option. If you need help understanding the PROC REPORT code or need help converting your current TABULATE code to REPORT code, you should contact Tech Support.
cynthia
Ask a Question
Discussion stats
  • 1 reply
  • 152 views
  • 0 likes
  • 2 in conversation