The SAS Output Delivery System and reporting techniques

Excel Sheet Name of Multiple Outputs from ODS TAGSET

Occasional Contributor
Posts: 14

Excel Sheet Name of Multiple Outputs from ODS TAGSET

I am outputting multiple excel files from ODS TAGSET. In each excel file, there are two sheets. The sheet name of the first output file is what I assigned. However, from the 2nd output file, each of the sheet names was added with "2", "3", etc.
Does anyone know how to let sas not insert the number to sheet names? Thanks.

Here is my code:

%do i = 1 %to 10;
ods tagsets.excelxp file="C:\&&file&i...xls" style=Journal
options(sheet_name="Math" ');
proc print data=one;run;

ods tagsets excelxp options (sheet_Name="Reading");
proc print data=two;run;
ods tagsets.excelxp close;
Posts: 8,743

Re: Excel Sheet Name of Multiple Outputs from ODS TAGSET

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":
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;

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:
<?xml version="1.0" encoding="windows-1252"?>

<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-comSmiley Surprisedffice:spreadsheet"
xmlns:x="urn:schemas-microsoft-comSmiley Surprisedffice:excel"
xmlns:ss="urn:schemas-microsoft-comSmiley Surprisedffice:spreadsheet"
<DocumentProperties xmlns="urn:schemas-microsoft-comSmiley Surprisedffice">

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.

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation