The SAS Output Delivery System and reporting techniques

proc report - sometimes subtotal sometimes not

Reply
N/A
Posts: 0

proc report - sometimes subtotal sometimes not

Hi,

Is it possible with PROC REPORT to make a summary something like this?

Category 1
Subcat A
Subcat B
Subtotal category 1

Category 2
Subcat A
Subcat B
Subtotal category 2

Category 3 (no subcats, no subtotals, results and row header must appear all in one line)

Grand Total

Thanks,
Anita
Frequent Contributor
Posts: 102

Re: proc report - sometimes subtotal sometimes not

Posted in reply to deleted_user
What is the output destination?
N/A
Posts: 0

Re: proc report - sometimes subtotal sometimes not

Posted in reply to JackHamilton
The output destination is Excel.
N/A
Posts: 0

Re: proc report - sometimes subtotal sometimes not

Posted in reply to deleted_user
Oh yeah, I'm using the ExcelXP tagset.
Frequent Contributor
Posts: 102

Re: proc report - sometimes subtotal sometimes not

Posted in reply to deleted_user
I can get you most, but not all, of the way there. Perhaps someone else can chime in with what's missing:

=====
title;
options missing=' ';

ods tagsets.excelxp file='c:\temp\test.xls';
proc report data=sashelp.prdsale nocenter nowindows;
column country prodtype actual;
define country / group;
define prodtype / group;
define actual / sum;

break after country / summarize skip;
rbreak after / summarize skip;

compute country;
if country ne ' ' then
hold_country = country;
endcomp;

compute actual;
if hold_country = 'U.S.A.' and _break_ = ' ' then
do;
country = ' ';
prodtype = ' ';
actual.sum = .;
call define(_row_, 'style', 'style=[cellheight=0]');
end;
else if _break_ = '_RBREAK_' then
country = 'TOTAL';
endcomp;
run;

ods tagsets.excelxp close;
=====

The results in LISTING, HTML, and tagsets.ExcelXP are similar:

=====
Product
Country type Actual Sales
CANADA FURNITURE $97,864.00
OFFICE $149,126.00
CANADA $246,990.00

GERMANY FURNITURE $101,194.00
OFFICE $144,804.00
GERMANY $245,998.00



U.S.A. $237,349.00

TOTAL $730,337.00
=====

It's close to what you want, but in LISTING there are extra blank lines, and in HTML and ExcelXP there are extra blank rows. I was hoping that the CALL DEFINE would cause the blank rows to be hidden, but it doesn't.
SAS Super FREQ
Posts: 8,868

Re: proc report - sometimes subtotal sometimes not

Posted in reply to JackHamilton
Hi:
I believe you might want to use a data step program for this report. Even with a cellheight of 0, there's still a row there produced by the call define. The thing is that cellheight of 0 isn't really respected as 0 -- but as some minimum height that is internally calculated by ODS.

In PROC REPORT, although you can turn all the values on the row to blank, you can't really suppress rows. But in DATA step, you can decide whether to write out a row or not. Using the default datastep table template, you can write out skipped or blank lines between groups by using FILE PRINT ODS syntax. And, you can decided whether to write out a row or more than one row for a group, based on conditional logic.

cynthia
Frequent Contributor
Posts: 102

Re: proc report - sometimes subtotal sometimes not

Posted in reply to Cynthia_sas
In destinations that recognize CSS, it might be possible to set DISPLAY=none, and make the whole row invisible. I haven't tried it, and I don't know if Excel in particular would recognize that attribute.

"Allow rows to be made invisible" should be added to the suggestion list for PROC REPORT - there are many occasions when this might be helpful.

I vaguely recall that there has already been a suggestion for the ability to control subtotaling to a greater degree than is now possible.

A data step program would work, but I think that the ODS data step interface is still experimental in SAS 9.1.3, and you would need it for a report of any great complexity.
SAS Super FREQ
Posts: 8,868

Re: proc report - sometimes subtotal sometimes not

Posted in reply to JackHamilton
Hi:
The "free-format" syntax of DATA _NULL_ is still experimental. But to create basic tables -- either using the default table template for DATA _NULL_ or with a custom table template works just fine.

What PROC REPORT does is just TABULAR in structure. It is not free format and to simulate PROC REPORT does not need the new object dot syntax. See the data and programs below for some basic examples.

cynthia
[pre]

options nodate nonumber center missing=' ';
title;
footnote;
ods listing close;

** Make some data;
data tktdata;
length Dest $16 Type $15 Amt 8;
infile datalines;
input Dest $ Type $ Amt Date : mmddyy10.;
format date date9.;
return;
datalines;
CHICAGO TEL 100 01/10/2008
CHICAGO TEL 200 01/10/2008
GENEVA WEB 300 01/10/2008
GENEVA WEB 400 01/10/2008
LONDON TEL 500 01/10/2008
LONDON TEL 600 01/10/2008
LONDON WEB 700 01/10/2008
PARIS TEL 800 01/10/2008
PARIS WEB 900 01/10/2008
CHICAGO TEL 100 01/11/2008
CHICAGO TEL 200 01/11/2008
CHICAGO WEB 300 01/11/2008
LONDON WEB 400 01/11/2008
LONDON TEL 500 01/11/2008
LONDON TEL 600 01/11/2008
LONDON WEB 700 01/11/2008
PARIS TEL 800 01/11/2008
PARIS WEB 900 01/11/2008
;
run;

proc sort data=tktdata;
by type dest;
run;


** 1) Detail Report with Grand Total;

ods html file='datanull_1.html' style=sasweb;

data _null_;
set tktdata end=lastobs;
file print ods=(variables=(type dest amt date));
gtot + amt;
put _ods_;
if lastobs then put @1 'Total' @3 gtot;
format amt comma7.;
title1 '1) Detail Data with Grand Total';
run;

ods _all_ close;
title;
footnote;

** 2) Detail Report plus subtotals and grand tot;

ods html file='datanull_2.html' style=sasweb;

data _null_;
set tktdata end=lastobs;
by type dest;
file print ods=(variables=(type dest amt date));
if first.type then subtot = 0;
gtot + amt;
subtot + amt;
put _ods_;

if last.dest then do;
if dest ne 'PARIS' then put @1 ' ';
end;

if last.type then do;
put @1 'Sub-Tot' @3 subtot;
put ' ';
end;

if lastobs then put @1 'Total' @3 gtot;
format amt comma7.;
title1 '2) Detail Data with Sub-Totals and Grand Total';
run;

ods _all_ close;
title;
footnote;


** 3) Summary Report;
ods html file='datanull_3.html' style=sasweb;

data _null_;
set tktdata end=lastobs;
by type;
file print ods=(variables=(type amt ));
if first.type then subtot = 0;
gtot + amt;
subtot + amt;
if last.type then do;
put @1 type @2 subtot;
end;
if lastobs then put @1 'Total' @2 gtot;
format amt comma7.;
title1 '3) Summary Report';
title2 'Sub-Totals and Grand Total Only';
run;

ods _all_ close;


** 4) Custom Table Template;
ods path work.dtemp(update)
sashelp.tmplmst(read);

proc template;
define table dnull;
column type dest amt date;

define type;
header = 'Type';
blank_dups=on;
style=Header;
end;
define dest;
compute as propcase(dest);
style=Header;
blank_dups=on;
header = 'City';
end;
define amt;
header = 'Amount';
format=comma8.;
cellstyle type in ('Sub-Tot', 'Total') as Header,
_val_ gt 500 as Data{foreground=green},
1 as Data;
end;
define date;
header = 'Date';
format=yymmddp10.;
cellstyle type in ('Sub-Tot', 'Total') as Header,
1 as Data;
end;

end;
run;

ods html file='datanull_4.html' style=sasweb;

data _null_;
set tktdata end=lastobs;
by type dest;
file print ods=(template='dnull'
column=(type dest amt date));
if first.type then subtot = 0;
gtot + amt;
subtot + amt;
put _ods_;

if last.dest then do;
if dest ne 'PARIS' then put @1 ' ';
end;

if last.type then do;
put @1 'Sub-Tot' @3 subtot;
put ' ';
end;

if lastobs then put @1 'Total' @3 gtot;
format amt comma7.;
title1 '4) Custom Table Template';
run;

ods _all_ close;
title;
footnote;
ods listing;

ods path sasuser.templat(update)
sashelp.tmplmst(read);

[/pre]
Frequent Contributor
Posts: 102

Re: proc report - sometimes subtotal sometimes not

Posted in reply to Cynthia_sas
For basic examples, FILE PRINT ODS will work. But I use CALL DEFINE quite a bit, and at that point FILE PRINT ODS fails to satisfy.

Yes, it's possible to do some customization using style templates - but they are by far the most complicated and difficult to understand part of ODS, more difficult than tagsets.

I don't agree with your characterization of PROC REPORT as "just tabular". The number of columns in any given row may varying, depending on the report definition and the data. ACROSS variables in particular are tricky to reproduce in in a data step (not impossible, of course, but sufficiently difficult in the general case that I want to avoid doing it). A report that can be whipped out in an hour with PROC REPORT might take days with the data step.
SAS Super FREQ
Posts: 8,868

Re: proc report - sometimes subtotal sometimes not

Posted in reply to JackHamilton
Hi:
I agree with almost everything you say for complicated reports .. .especially those with a lot of CALL DEFINES or ACROSS or COMPUTE blocks. But for the original problem program output in this forum posting, I think that DATA _NULL_ is a viable alternative.

It's never taken me days to work out a DATA _NULL_ to compare it to a PROC REPORT. Once you understand that N=PS still works with ODS and DATA _NULL_ and array processing still works and that if you have generic columns, you can write a variable number of ACROSS columns -- a lot is possible. In my last SGF paper, I showed creating a standard demographic report using both PROC REPORT and DATA _NULL_...the only thing different was that the PROC REPORT data needed some pre-PROC massaging and the DATA _NULL_ version of the program needed a custom Table template and N=PS to work.

I used a custom Table template in my example, not a custom style template. I think that custom Table templates added to DATA _NULL_ -- especially with the ability to define generic columns is a really neat feature of ODS. And, since a Table template allows you to change the style elements for the output and do traffic lighting, it has a lot of the features of call define (although, PROC REPORT can do FORMAT changes in a CALL DEFINE and Table templates can't do that directly.)

In fact, Table template syntax is more like PROC REPORT syntax, than any of the other template syntaxes (in my opinion). Both Tagset templates and Table templates have define blocks and Table templates have a COLUMN statement like PROC REPORT.

cynthia

Papers:
http://nesug.org/Proceedings/nesug05/dp/dp14.pdf
http://www.lexjansen.com/pharmasug/2008/sas/sa08.pdf
Ask a Question
Discussion stats
  • 9 replies
  • 442 views
  • 0 likes
  • 3 in conversation