BookmarkSubscribeRSS Feed
flyingsohigh
Calcite | Level 5
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;
%end;
ods tagsets.excelxp close;
1 REPLY 1
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1714 views
  • 0 likes
  • 2 in conversation