The SAS Output Delivery System and reporting techniques


Posts: 52


I'm just beginning to use EXCELXP with PROC REPORT. I have tons of legacy detailed PROC REPORTs that I would like to keep intact--and so would my customers. Is there an easy way to get the summary lines of BREAK or RBREAK to work like the SUM statement in PROC PRINT?

Kim LeBouton
Posts: 8,743


I'm not sure what you you want the break lines from PROC REPORT to -look- or -be- more like the HEADERs (bigger font, diff colors, etc)?

The only difference that I see is the SUM line for PROC PRINT seems to be in the header style (when you use STYLES.DEFAULT) for PROC PRINT and the BREAK/RBREAK line seems to be in italic for PROC REPORT. This is easily changed.

PROC REPORT allows extensive formatting of EVERY report component -- LINES, SUMMARIES, CALL DEFINE, HEADERS, etc. (Well, PRINT and TABULATE do too...but you were asking about REPORT.) Test the following code.

** the code;[pre]
ods tagsets.excelxp file='c:\temp\testit.xls';
proc print data=sashelp.class noobs;
var name age height;
sum age height;

proc report data=sashelp.class nowd
column name age height;
rbreak after /summarize;
ods tagsets.excelxp close;

When I run that code, the summary lines look the same. If you meant something other than this can you give an example of what you mean by "to work like the SUM statement".
Posts: 52


I'm having to retrace my testing yesterday, but I ran a similar example, and the sum line with PROC PRINT produced the sum function in Excel. I must have downloaded a template (from the SAS site) that produced the traditional sum Excel. I haven't found the template yet.

I guest my new question is as follows: Can I produce an Excel sum function with PROC REPORT?

Your example does produce the same results.

Kim LeBouton
Posts: 8,743


I think I've got want to send an Excel formula from SAS to Excel????

I just checked the internal documentation for the newest tagset and saw the Auto_SubTotals option. It's documented in the ExcelXP internal help as only working with PROC PRINT and, indeed, when I try it PROC PRINT has a subtotal formula on the SUM row, but PROC REPORT does not.

I suspect that this is due to the fact that PROC PRINT and PROC REPORT probably do not send the same event for their summary rows -- so the piece of the tagset that works for PROC PRINT does not work for PROC REPORT (because they are not using the same event). I do not know whether there's a way to fix this for SAS 9.1.3 -- if there is a way to fix it, I suspect it requires PROC TEMPLATE and making a change to the ExcelXP tagset template (IF there's a way to fix it).

I understand from talking to the ExcelXP folks that this situation (trigger the same events for PRINT vs REPORT) improves in SAS 9.2.

But meanwhile, as to the definitive answer about whether there's a way to change the tagset so PROC REPORT produces the same formula as PROC PRINT with AUTO_SUBTOTALS='YES', we may have to wait for one of the ExcelXP gurus to check the postings and see if he/they have an idea.

SAS Employee
Posts: 95


Hello Kim,

proc report does present some problems when it comes to creating a subtotal function. The first part of the problem is detecting which row the subtotal starts on, and then, where it should go when the time comes.

If you know how many rows you have, then we can send a formula as a sytle over ride on the break. Being proc report, we could count the rows. But all of that seems like a lot of work. But it would work and work well.

We could modify the tagset so that it could signalled, where to start, stop and where the subtotal should go. Using a style over ride like we do for rotate, format, formula and type, could enable us to do everything you want. The only difference is the tagset would do the counting, and keep
track of which columns get subtotals.

The problem is that proc report has to tell the tagset where to put the subtotal and what rows to put in the subtotal().
Fixing up the tagset to follow guides
given as over rides would work well. But it does take some proc report code to make it work...

With proc print, it is still a little touchy and doesn't always work. The tagset is doing it's best to guess. Multiple totals, and bygroups that
mix in the same table will break it. The way it guesses is it looks at
the table section. Anything that is in the foot portion of the table is a
total. It also starts counting at the first row under the headers. So it knows where everything starts. This is not a perfect methodology. But
it works most of the time.

We've discussed having proc print and report give better clues so that the
tagset can accurately and automatically add subtotal functions. But that will not be investigated until the SAS release after this next one.

Because the tagsets are malleable, we can make them do just about anything.

Let me know if you would like to investigate these idea's. Ultimately proc report is much more capable at doing this sort of thing than proc print.
So there are more options available to us.
Ask a Question
Discussion stats
  • 4 replies
  • 3 in conversation