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]
Click image to register for webinar
