BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
OS2Rules
Obsidian | Level 7
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.
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
Hi!
This code works for me in TAGSETS.EXCELXP to generate the equivalent of the skipped line after the BREAK.
[pre]
proc sort data=sashelp.shoes
out=shoes;
by region;
where region in ('Asia', 'Canada', 'Pacific');
run;
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
style(summary)={font_weight=bold};
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 ' ';
endcomp;
run;
ods tagsets.excelxp close;
[/pre]

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,
cynthia

View solution in original post

6 REPLIES 6
Cynthia_sas
SAS Super FREQ
Hi!
This code works for me in TAGSETS.EXCELXP to generate the equivalent of the skipped line after the BREAK.
[pre]
proc sort data=sashelp.shoes
out=shoes;
by region;
where region in ('Asia', 'Canada', 'Pacific');
run;
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
style(summary)={font_weight=bold};
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 ' ';
endcomp;
run;
ods tagsets.excelxp close;
[/pre]

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,
cynthia
himself
Quartz | Level 8
Hi Cynthia I came across this while reading the book The SAS® Programmer’s
PROC REPORT
Handbook
Basic to Advanced Reporting Techniques
by Jane Eslinger and I qoute
"Page breaks are not straightforward in other destinations. HTML output is
designed to be viewed on a screen, and the amount of the report that can be
seen is limited only by the size of your monitor. Scroll bars are used to see
output that is extremely wide or long. Destinations such as PDF or RTF are
true “paging” destinations and are meant to be printed. Printed output is
limited to the size of the paper. However, the amount of output that fits on
one page is extremely variable. Titles, footnotes, logos, margins, font face,
font size, and borders all affect the output"
Cynthia_sas
SAS Super FREQ
Hi:
Thanks for this. Yes, Jane's book is very good and she explains the limitations very well. I'm not sure why you have added to a 13 year old posting, so if you have a new question, you might want to open a new thread in the Forum. Since 2007, we now have ODS EXCEL in addition to TAGSETS.EXCELXP. What Jane wrote is still true, page-breaking is really only guaranteed to work the way it is designed in "paged" destinations like RTF and PDF. ODS HTML, ODS TAGSETS.EXCELXP and ODS EXCEL are NOT paged destinations. The original question was about how to do the equivalent of the SKIP option in PROC REPORT and my code showed how to do that.
Cynthia
OS2Rules
Obsidian | Level 7
Cynthia:

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?
Cynthia_sas
SAS Super FREQ
Hi:
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]



[/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!
cynthia
OS2Rules
Obsidian | Level 7
Cynthia:

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']");
endcomp;

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3409 views
  • 2 likes
  • 3 in conversation