proc report: creating an excelsheet by ods

[ahnungslos]

This creates an Excel-file with ods:

data t1;

length city $7;

land='F'; city='Paris'; value=100; output;

land='F'; city='Lyon'; value=200; output;

land='D'; city='Berlin'; value=100; output;

land='D'; city='Hamburg'; value=200; output;

run;

ods listing close;

ods html body="c:\test.xls";

proc report data=t1 nowd;

column land city value;

define land / group;

define city / group;

define value / sum;

break after land / summarize page;

run;

ods html close;

ods listing;

This does not create a pagebreak in the resulting Excelsheet. How can I create a real pagebreak in Excel?

Thank you

[Cynthia_sas]

Hi:

  First, a somewhat curmudgeonly caveat: When you use ODS methods to create ASCII text files (such as ODS HTML, ODS MSOFFICE2K, ODS CSV or ODS TAGSETS.EXCELXP), even IF you name the files with the .XLS extension, you are NOT creating "true, binary" Excel workbooks, which contain worksheets. You are creating ASCII text files that Excel knows how to open and render. And, for each type of ASCII text file that you create, Excel has a certain rendering "logic" that it follows:

--ODS CSV: Excel opens this comma delimited file and renders it very plainly without any colors or fonts or cosmetics

--ODS HTML: Excel opens this HTML file and renders it as it interprets the HTML 4.0 tags -- generally, this means that Excel opens multiple outputs into 1 single sheet and it retains some rudimentary style information, but Excel did not "like" the W3C HTML 4.0 specification. So instead they made their own "flavor" of HTML

--ODS MSOFFICE2K: Excel opens this HTML file and renders it as it interprets the Microsoft "flavor" of HTML -- generally if you use a SAS style in your invocation, your style template is used to impact the color and fonts of the output, when shown in Excel. Every table that you crreate still goes into one huge sheet, and you have limited ways to exert inflluence over items like orientation, and other Excel-specific controls.

--ODS MSOFFICE2K_X: This is a second type of Microsoft HTML. It has a little bit of Microsoft XML added in. Excel opens and renders this file in about the same way as it does the MSOFFICE2K HTML file. The difference between the two destinations is that you have more control, with this destinatioin over some of the Excel-specific controls, such as orientation, etc.

--ODS TAGSETS.EXCELXP: This is Microsoft Spreadsheet Markup Language XML as formulated by Microsoft for Office 2003. This ODS destination will create an ASCII text file -- in XML format that Excel opens and renders. By default, every table that you create (such as those created by the PAGE option or for every procedure output or for every by group) would become a new sheet, when Excel opens and renders the ASCII text XML file.

  So, I don't know exactly what you mean by a "real" pagebreak in Excel. If you used TAGSETS.EXCELXP then you should find that every PAGE options generates or causes a new sheet to be displayed for you. I don't think you could ever get this with ODS HTML.

  You can prove to yourself what is "inside" the file you create by creating your file and then opening the file with Notepad. For the issue of "page breaks", you will have to be more clear what you mean, but I believe that you will need to switch to ODS TAGSETS.EXCELXP to get multi-sheet workbooks when the XML file is opened and rendered with Excel.

cynthia