Hi;
The only way to make a multi-sheet workbook is to use
1) the LIBNAME engine for Excel or PROC EXPORT (but those need a SAS dataset) or
2) use ODS TAGSETS.EXCELXP to make an XML file (Spreadsheet Markup Language XML from Excel 2003 specification) that Excel knows how to open and render.
The only "hitch" in this scenario is that while TAGSETS.EXCELXP can make an XML version of a multi-sheet workbook, it does have 1 limitation. When you try to put graphic output into the XML file, such as with PROC GCHART, TAGSETS.EXCELXP gives you the following message:
Excel XML does not support output from Proc:Gchart
Output will not be created.
because, by
MICROSOFT design, the XML file CANNOT contain graphical output from SAS (or any graphical output, like a logo, for that matter). So as long as you only want the report TABLES from PROC REG analysis, you'll be OK.
The basic way to invoke TAGSETS.EXCELXP given the above PROC REG code and macro invocation is this:
[pre]
ods tagsets.excelxp file='c:\temp\whole_regress.xls' style=sasweb
options(doc='Help');
** should be whole dataset (BY commented out);
%let reg =;
%reg21;
** should be by sex (BY SEX in effect);
%let reg = use_sex;
%reg21;
** should be by other (BY OTHER in effect);
%let reg = use_oth;
%reg21;
ods tagsets.excelxp close;
[/pre]
The doc='Help' suboption will put a list of valid suboptions into the SAS log. Note that you must have at least SAS 9 to use TAGSETS.EXCELXP and at least Excel 2003 to open and render the XML file correctly.
Also note the fact that I give the file an extension of .XLS for convenience purposes only -- so Windows will launch Excel when I double-click on the file name, c:\temp\whole_regress.xls -- I am merely "fooling" the Windows registry with this file extension trick. The actual file extension is .XML (because TAGSETS.EXCELXP creates an XML file, but if you double-click on a file that has an extension of XML, usually a browser will try to open the file. (The correct file extension for files created with TAGSETS.EXCELXP is either .XML or .XLS -- do not be tempted to name the file .XLSX -- that file extension is reserved for a different "flavor" of Excel XML (Office 2007). TAGSETS.EXCELXP creates Office 2003 "flavor" of XML.)
Also note that when you open the XML file with Excel 2007 or 2010, you may receive a warning message similar to the one described in this Tech Support note (note describes the situation when you give an HTML file the extension of .XLS):
http://support.sas.com/kb/31/956.html
For more information about how to use some of the TAGSETS.EXCELXP sub-options, this is an excellent user-group paper:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf
For more information and a good overview of SAS Macro processing, this is an excellent paper:
http://www2.sas.com/proceedings/sugi28/056-28.pdf
cynthia