Hi:
My comments under your questions.
1. I'd like to output multiple reports to the same workbook, but different sheets.
--> This -IS- possible with ODS HTML, but is sort of a pain to manage. The preferred method to create multiple sheets in one Workbook is to switch to ODS TAGSETS.EXCELXP instead of ODS HTML. If you MUST stick with ODS HTML then you have to follow the steps outlined in this SUGI paper with ODS HTML:
http://support.sas.com/rnd/base/ods/excel/multisheet_excel_post.ppt
However, TAGSETS.EXCELXP creates Microsoft Spreadsheet Markup Language XML and will create multiple worksheets automatically. The only "downside" is that SpreadsheetML does not support the insertion of graphic images (by Microsoft design).
2. I'd like to tell Excel to "merge cells" so that my title lines and footnotes don't wrap inside the first column.
--> Try ODS MSOFFICE2K (Microsoft-compliant "flavor" of HTML) or, again, try TAGSETS.EXCELXP (Microsoft-compliant "flavor" of XML) -- which actually puts your titles into either the header of the document OR embedded into the
worksheet(s).
3. I use the parameter - htmlstyle="mso-number-format:\@ on the style(column) statement to avoid having large numeric values, such as account numbers, be in exponential format. But, I'd like to limit this to specific column rather than all numerics in the report.
--> If you are using PROC PRINT, PROC REPORT or PROC TABULATE, then you can specify the HTMLSTYLE attribute on a DEFINE or VAR statement or crossed with a variable in a TABLE statement, so the M-soft format only affects one column. Also, with TAGSETS.EXCELXP, (if you decided to switch), you can still use mso-number-formats -- only you use them with the TAGATTR style attribute instead of the HTMLSTYLE attribute.
4. After generating my report, I have to save it as an Excel Workbook, since the default is Web Page. I'd like to have the default be an Excel Workbook.
--> Ah, this is a Microsoft "feature". It is Microsoft letting you know that IT knows that you tried to fool it.
ODS does not create a "true binary" .XLS format files -- it creates either CSV or HTML or XML "flavor" of files -- all of these 3 types of files are ASCII text files. No matter what file extension you give them, if you open the files you create in Notepad, you should see HTML tags or XML tags in the file.
You only "fool" the Windows registry by naming the extension with .XLS so Excel launches instead of a browser. Microsoft "knows" that you did not send it a "true" XLS file and that becomes apparent when you go to save. In fact, in Office 2007, Microsoft grumbles a bit when you try to -open- an HTML or XML file that you named with .XLS -- and Excel pops open an annoying window to which you MUST respond 'YES' in order to open the file.
The only workaround I've heard for the "save as" issue is to run a "batch" VB script that opens the file and does the SAVE AS .XLS for you.
cynthia