The SAS Output Delivery System and reporting techniques

Skip a line in PROC REPORT

Super Contributor
Posts: 358

Skip a line in PROC REPORT

Hi All:

I have problem that I can't seem to figure ...

I would like to insert a blank line in a Proc Report output after a subtotal that is calculated in a 'BREAK AFTER'. The thing is, I'm using ODS and the EXCELXP tagset to create a Excel spreadsheet.

SAS Note SN-002549 notes that some of the PROC REPORT options are not supported by ODS and provides a work-around. These don't seem to work with this tagset, unfortunately.

I've contacted SAS support and they basically told me 'you can't get there from here', ie: this is not supported (yet).

I've tried just about everyhting I can in the PROC REPORT using STYLE options, etc.

I'm sure there has to be some way to do this other than adding a blank line to the data in the appropriate place so that the output 'looks right'. Perhaps the CELLHEIGHT can be changed in the tagset for the SUMMARY lines, or something like that. I don't claim to be clever enough to know how to make those kind of changes.

Any suggestions would be greatly appreciated.
Posts: 9,368

Re: Skip a line in PROC REPORT

This code works for me in TAGSETS.EXCELXP to generate the equivalent of the skipped line after the BREAK.
proc sort
by region;
where region in ('Asia', 'Canada', 'Pacific');
ods listing close;

** Begin Use a Line Statement **************;
** Example: use a line statement to write out;
** a blank line...but by default, the line will be thick;
** and that may be undesirable;

ods tagsets.excelxp file='c:\temp\simskip1.xls' style=sasweb;
title 'Simulate Skip Option 1';
title2 'puts a blank line after region';
proc report data=shoes nowd
column region subsidiary sales;
define region /group;
define subsidiary /group;
define sales / sum;
break after region / summarize;
compute after region /
style(lines)={font_size=1pt cellpadding=0 cellheight=1pt};
line ' ';
ods tagsets.excelxp close;

It seems to me that Excel is ignoring my cellheight (which works quite nicely in HTML output), but since tagsets.excelxp is generating Spreadsheet Markup Language XML, I can live with having the skip there since I can do things in Excel to make the skipped line smaller.

Good luck,
Super Contributor
Posts: 358

Re: Skip a line in PROC REPORT


Thanks for the prompt reply.

I tried your code and got messed up by my tagset (which has some minor changes) and my template (which has a lot of changes). I finally got it to run and it does work as advertised.

But .....

When the blank line is inserted into the sheet, it removes all of the formatting data that I have added to the columns (to make the report pretty for the client). I don't think that this solution has the flexibility to allow for the blank line AND the formats, so ....

Is there a way to make the first printed line after a break double height? This would retain the formatting of the cell and would give the apperance of a blank between the subtotal and the next line of data. Again, I'm just guessing whether this is even possible.

Looking at the generated XML code, there is a height specification on each table row created - is there a way to just change this height on the selected line?
Posts: 9,368

Re: Skip a line in PROC REPORT

I think you would have to change the tagset to do that. According to my XML when I generate the output, the LINE statement writes this XML in ONE cell, not in 3 cells:[pre]

So I don't know what formatting you might want in the blank line?? I think the only way to accomplish this would be to alter the event that writes the blank line. Especially if you mean that you only want the double height applied on the FIRST (and only the FIRST) line after the break -- you'd have to set a flag variable in the tagset to write one kind of XML when the counter for the break was 1 and write some other kind of XML when the counter was GT 1. I am only speculating here -- I don't know Spreadsheet ML (the Microsoft spec) well enough to understand how to make this happen.

But, your first hurdle is this, in my way of thinking: I wrote output that had 3 columns -- but, at the break, I have one single cell spanning those 3 columns. so if you wanted just the TOTAL SALES column to be one color and the REGION column to be a different color (for example) -- you can't do that the way the LINE statement and ExcelXP are building that cell right now.

To me, this seems to be a question for Tech Support, because they can look at the kind of formatting that you need to accomplish and they can keep a track open for you while the issue is resolved.

BTW, you referred to "printing" in your post -- I'm only viewing this in Excel, I don't know what will happen when you actually go to print the spreadsheet. Since the LINE is controlled by the NoteContent Style Element, you might try to make that element have a larger cellheight in the style template -- but that still does not fix the fact that the line is 1 cell and not 3 cells.

Good luck!
Super Contributor
Posts: 358

Re: Skip a line in PROC REPORT


Thanks for all the help so far.

The problem that I have is that my spreadsheet has some columns formatted with left and right borders for the my clients - just for readability. When I use some style options in a COMPUTE block, I loose the formats.

(I've discussed this with SAS support and it has simthing to do with EXPLICIT and IMPLICIT style elements, but it was all hocus-pocus to me by that time).

What I really need is a way to specify multiple style elements on the same line. If I have a line:

compute difference;
if seq = 13 then call define(_col_,'style',"style=[tagattr='format:#,##0.0000']");

Then I can also tell it to also draw a line at the top of the cell so that this would look like a total line to the user. (I've seen how this is done, but I can't find it any more.)

I could also use the same technique to retain the column style elements that I use on the DEFINE statement in the COMPUTE block.
Ask a Question
Discussion stats
  • 4 replies
  • 2 in conversation