The SAS Output Delivery System and reporting techniques

excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

Reply
New Contributor
Posts: 4

excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

I just started using the excelxp tagset and I've succeeded in having it create multiple worksheets within a workbook, with customized headers, and lots of other nifty features. Since I am trying to duplicate an existing Excel output format, now I need to force hard page breaks, within worksheets, when the value of a variable changes. The goal is to be able to print the worksheet and have each value of this variable start on a new page. I'm using proc report, and have tried using a compute block to at least add a line at the break point (I figure if I can output a line, I can then replace it with the actual XML code for a hard page break, once I find out what that is!) but I get an error message that there is a problem with the table when I try to open the file in Excel. Here's my code:

ods path work.cas(update) sasuser.templat(update) sashelp.tmplmst(read);
proc template;
define style styles.CASXL; * just creating a copy;
parent=styles.sansprinter;
end;run;quit;

%let title1 = "&campaigndesc - from &strtdt to &enddt";
%let title2 = "Data as of &asofdt";

ods listing close;
ods tagsets.excelxp file="&campaignpath.\&sharepointfolder.\&distnm._&gekkofileprefix.&mth..xml"
options(embedded_titles='yes'
frozen_headers='4'
orientation = 'Landscape'
suppress_bylines='Yes'
sheet_interval='bygroup'
autofit_height='Yes'
pages_fitwidth='1'
row_heights='0,0,0,30,0,0,0'
sheet_label=' '
pagebreaks='no'
) style=CASXL;
title1 j=center font='Ariel bold' height=18pt &title1;
title2 j=center height=12pt &title2;
proc report data=final nowindows
style(header) = {background=cx99ccff foreground=black font_size = 8pt FONT_WEIGHT = bold}
style(column) = {font_size = 8pt};
by pntname;
column pntname offname fcoff fcno FCNAME Client_Name acctnum2
numsecs totcddolX earliestmatdte hhasx &specialist1 pthcorid;
define pntname / display order 'Complex' style(column) = {cellwidth=100pt } ;
define offname / display order 'Office Name' ;
define fcoff / display order 'Office Number' style(column) = {cellwidth=50pt } ;
define fcno / display order 'FA Number' style(column) = {tagattr='format:@' cellwidth=50pt } ;
define fcname / display order 'FA Name';
define Client_name / display order 'Client Name';
define acctnum2 / display 'Account Number' style(column) = {tagattr='format:@' cellwidth=50pt } ;
define numsecs / format=comma6.0 'Number of Maturing CDs' style(column) = {cellwidth=50pt } ;
define totcddolX / style(column) = {tagattr='format:$##,###,###,##0' cellwidth=50pt }
'Total Market Value ($)' ;
define earliestmatdte / 'Earliest Maturity Date' style(column) = {cellwidth=50pt } ;
define hhasx / display style(column) = {tagattr='format:$##,###,###,##0' cellwidth=50pt }
'Total HH Assets ($)' ;
define &specialist1 / display 'Specialist' style(column) = {cellwidth=100pt } ;
define pthcorid / display 'PATH Core ID' style(column) = {tagattr='format:@' cellwidth=100pt } ;
compute after fcname ;
line ' ';
endcomp;
run;
ods tagsets.excelxp close;
ods listing;
run;
SAS Super FREQ
Posts: 8,743

Re: excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

Hi:
When I manually go into an Excel sheet and insert "hard" page breaks and then save the file as an XML 2003 file, this is what I observe -- the XML has a PageBreaks tag like this:
[pre]
<PageBreaks xmlns="urn:schemas-microsoft-comSmiley Surprisedffice:excel">
<RowBreaks>
<RowBreak>
<Row>10</Row>
</RowBreak>
</RowBreaks>
</PageBreaks>
</Worksheet>
[/pre]

And the <PageBreaks> </PageBreaks> tags enclose a <Row> tag that contains the row number (in this case, 10) where I wanted the "hard" page break.

The only way I know to insert XML or alter the XML that's created by TAGSETS.EXCELXP is to
1) change the tagset template using PROC TEMPLATE. This is not a trivial task. or
2) post-process the XML to manually or programmatically insert the XML that's needed. This is not a trivial task either.

I would suggest that you experiment with the FITTOPAGE or FITTOWIDTH suboptions to see whether you can force a page break the way you want or work with Tech Support on a tagset template solution. To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm

cynthia
New Contributor
Posts: 4

Re: excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

Hi Cynthia,

Thanks for your reply - let me see if I understand this - I could post-process the XML, and add the PageBreaks tag to each worksheet, specifying at which Row I want each page break? That sounds do-able - I know where the breaks should be, after each change in one of my variables. I can count how many rows each group is, add the header rows, and generate a list of row locations for the page breaks. Then, I just have to write out the PageBreak tag block, at the end of every worksheet in the workbook. Can I do that by reading the XML back into SAS, find each end of worksheet tag, and write it back out, adding the page break blocks in? Sounds like a challenge, but possible.

If I do it this way, I don't need the 'Compute after' compute block, do I? I thought I'd have to put the page break physically where I wanted it to occur. Putting them all at the end of the worksheet is easier.

Again, thanks for your help!

Cynthia
SAS Super FREQ
Posts: 8,743

Re: excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

Hi:
What you describe is do-able with a program. You can read an XML file with a SAS program and then write out an XML file.

If you are going to put more than 1 hard page break into the file, then you have to take into account whether embedded titles or more than 1 header rows are included, because this may have an impact on the row number that you use for the break. If I manually insert 2 hard page breaks into an Excel worksheet and then save the workbook/worksheet as Excel 2003 XML, the <PageBreaks> section looks like this:
[pre]
<PageBreaks xmlns="urn:schemas-microsoft-comSmiley Surprisedffice:excel">
<RowBreaks>
<RowBreak>
<Row>3</Row>
</RowBreak>
<RowBreak>
<Row>15</Row>
</RowBreak>
</RowBreaks>
</PageBreaks>
[/pre]

Note the repetition of the <RowBreak> tags within one <PageBreaks> section.

cynthia
New Contributor
Posts: 4

Re: excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

Thanks, yes I will have to compensate for any additional lines used by titles and header rows.

Is there anything I should know about reading in the XML? Is it just a text file? Can you point me to a sample, to get me started?

This is great - once I get this working, it will allow me to eliminate the use of a very tedious Excel macro, that takes very long to run. SAS will produce my output, formatted beautifully, all ready to be sent to the client! Such an improvement since the old listing days! :-)
SAS Super FREQ
Posts: 8,743

Re: excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

Hi:
An XML file is just an ASCII text file. Basically, you're going to read a line and write a line until you encounter the </Worksheet> tag. At that point, you will insert the <PageBreaks> info for every worksheet.

The program shown in this previous forum posting:
http://support.sas.com/forums/thread.jspa?messageID=16808䆨

illustrates how to read an ASCII text file (not an XML file -- but a .SAS program). However, tne concepts for reading the output are the same. What will be different is that the program in the above posting uses a SAS dataset to hold an entire .SAS program. You would not need to do that -- you can just read a line of XML and write a line of XML to a different file. So you could use DATA _NULL_ for your program.

As to how you would collect your break points, I would imagine that you can figure out what row in the final file should be the break point. If PROC REPORT is generating a detail report, it's probably easier than with a summary report, but in either case, I'd be tempted to use PROC REPORT to create an output dataset in order to collect the correct row numbers for creaing the <RowBreak> sets of tags.

The best thing to do would be for you to create your XML file without the page breaks and then to examine the XML file using Notepad. That will give you an idea of how the tags will look and some idea of what you should be looking for to distinguish one worksheet's markup XML from another worksheet's markup XML as you're reading through the file.

Then, I'd recommend writing a program that just reads a line and writes a line and get that logic working before you introduce other elements. Once that works, I'd figure out how to find the </Worksheet> tag using the INDEX or FIND functions and once you get that working, write the correct PUT statement logic to write out the <PageBreaks> set of tags.

It would be far easier/better/less hassle to create a worksheet for every logical group -- which automatically starts on every page. So that when they printed the entire workbook, every group would be on a page. When I used the code below, I got every table/page on a separate worksheet and then when I printed, I had to select "Entire Workbook" on the print window, but I got every "group" on a new page. Would something like that work??

cynthia
[pre]
proc sort data=sashelp.shoes out=shoes;
by region;
where region in ('Africa', 'Asia', 'Canada','Pacific');
run;

title;
footnote;
** Create Excel 2003 XML file with a sheet for every table;
** this is the default TAGSETS.EXCELXP behavior;
** Have to print "Entire Workbook" to see separate pages;
ods tagsets.excelxp file='c:\temp\makegrp_page.xls' style=sasweb
options(doc='Help' row_repeat='1');

ods tagsets.excelxp options(sheet_name="Shoes");
proc report data=shoes nowd;
column region product sales;
define region / group;
define product / group;
define sales / sum;
break after region / summarize page;
run;

** make second sheet grouped by age;
ods tagsets.excelxp options(sheet_name="Class");

proc report data=sashelp.class nowd;
column age name sex height weight;
define age / order;
define name / order;
define sex / display;
define height/mean;
define weight/mean;
break after age / summarize page;
run;
ods _all_ close;
[/pre]
New Contributor
Posts: 4

Re: excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

Hi Cynthia,

Just wanted to give you an update on this project. I went ahead with the task of post-processing the XML to add the hard page breaks.
After writing out the multisheet XML file by using a proc report step with a by group as the interval, I then read that XML file back in and add hard page breaks at a sub-sheet level. I build a dataset with the row break values, find where each sheet ends, add all of the pagebkeak/rowbreak blocks to the xml and then write it out again. As you mentioned, yes, this is tedious, but it works! I've even added a dde step at the end to open the xml, apply a password, and save it as a native XLS. All from within SAS - pretty neat.

Thanks for your help,
Cynthia
SAS Super FREQ
Posts: 8,743

Re: excelxp tagset, multisheet workbooks, need hard page breaks within a sheet!

Oh, I'm so glad it worked out. Yes, it is a chore to collect all the info, but the XML you have to add is straightforward and that's what the DATA step is good at!

Congratulations on figuring it all out and making it work!

cynthia
Ask a Question
Discussion stats
  • 7 replies
  • 269 views
  • 0 likes
  • 2 in conversation