Hi:
What a difference a single "dot" can make. Without involving any thing to do with a Macro %DO loop, this code worked to make one sheet say "Math" and the other sheet say "Reading":
[pre]
ods tagsets.excelxp file="C:\temp\testname.xls" style=Journal
options(sheet_name="Math" );
proc print data=sashelp.class(obs=3);run;
ods tagsets.excelxp options(sheet_name="Reading");
proc print data=sashelp.class(obs=3);run;
ods tagsets.excelxp close;
[/pre]
the only difference between my code and your code (aside from the macro statements) is the fact that I have
ods tagsets.excelxp options(sheet_name="Reading") versus
ods tagsets excelxp options(sheet_name="Reading")
(you have an incorrect invocation of ODS TAGSETS.EXCELXP when you try to set the second sheet name. The correct invocation method is:
ODS TAGSETS.EXCELXP (it is a 2 level name) and not ODS TAGSETS (space) EXCELXP. An alternate invocation is: ODS MARKUP TAGSET=EXCELXP -- but if you use that invocation, then you must close the file with ODS MARKUP CLOSE;
And just FYI, you said you were outputting multiple excel files, but, when you use ODS TAGSETS.EXCELXP, you are not creating a true, binary .XLS file. You are creating Microsoft Office 2002/2003 Spreadsheet Markup Language XML. If you name the file with a .XLS extension, you are only doing this for the convenience of people who double-click on the file name in Windows. The file is XML -- Microsoft-specific XML -- and you can prove this to yourself by opening the file you create with NOTEPAD and look at the XML. The first few lines of the created XML file are:
[pre]
<?xml version="1.0" encoding="windows-1252"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office">
[/pre]
Now, this is NOT a big deal that the file is XML instead of "true" .XLS -- until someone wants to CHANGE and RESAVE the file. If they just do a simple SAVE, then Excel will resave the file as XML and if they want the file saved as a true XLS or XLSX file, then they need to do a SAVE AS and change the file type from XML to the file type they want.
As I said at the beginning, I'm confused by what your macro %DO loop is doing. It looks like you are planning to generate the same 2 PROC PRINT outputs 10 times.
cynthia