The SAS Output Delivery System and reporting techniques

how to insert more then 2 excel sheets

SAS Employee
Posts: 17

how to insert more then 2 excel sheets

Can any one help me out in this aspect. I am using SAS EG4 and working on a proc report.
I want to export 2 proc reports on 2 different sheets of a excel file.
Currently I am using the following command to export excel file to server.
ODS HTML BODY="/sas/<>/<>/BLAV_LIB.xls"

how can i modify this command or use some other, please suggest me.
Super Contributor
Posts: 259

Re: how to insert more then 2 excel sheets

Have you tried using excelxp-tagset instead of html?

[pre]ods tagstes.excelxp file="...";
proc report ...

proc report ...

ods tagsets.excelxp close;[/pre]
Some examples are explained on
SAS Employee
Posts: 17

Re: how to insert more then 2 excel sheets

Yes I tried, but I want the output in excel format only ,not in xlm format
Can you suggest me how I can get it.
Posts: 8,743

Re: how to insert more then 2 excel sheets

If you look at your file:

with Notepad or any TEXT editor you will see the ODS HTML is NOT making you a true, binary .XLS file. When you use ODS HTML, you are creating an HTML 4.01 ASCII text file. The .XLS extension in the above code is ONLY acting to fool the Windows registry into launching Excel when you double-click on the file created by ODS HTML.

In some ways, TAGSETS.EXCELXP is creating -more- of an Excel file, because the XML created by ODS conforms or follows the Microsoft Spreadsheet Markup Language XML specification, as laid out -- by Microsoft -- for Office 2002/2003.

So, your statement that you want the output in "excel format" would only, truly be satisfied by using PROC EXPORT or the Excel LIBNAME engine. However, if you use either of those methods, the trade-off is that you will have a true, binary, .xls file, but you will not be able to set any fonts or formatting or colors inside the Excel file with SAS.

On the other hand, if you use ODS HTML or ODS TAGSETS.EXCELXP, you have the ability to affect fonts and colors. The difference between ODS HTML and ODS TAGSETS.EXCELXP -- besides the general difference between the more general HTML markup and the more specific Spreadsheet markup is that TAGSETS.EXCELXP automatically gives you the ability to create multiple sheets in one workbook. You do not have this capability, automatically, with ODS HTML methods.

Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation