The SAS Output Delivery System and reporting techniques

ODS- Saving Excel file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

ODS- Saving Excel file

Hi All,

I am using ODS HTML and ODS TAGSETS to create  Excel files.  I am facing problem when opening output excel.

Whenever I open the excel sheet i have pop up stating "You are trying to open,filename.xls  is in a different format than specified by file extension."

I get this error as SAS store data in HTML format. Is there any way to avoid getting this error?

Thanks in Advance


Accepted Solutions
Solution
‎09-11-2012 01:18 PM
SAS Super FREQ
Posts: 8,740

Re: ODS- Saving Excel file

Hi:

  That message is Microsoft's way of letting you know that it wants you to use the "correct" file extension for output. So, technically, they are correct, ODS HTML creates HTML files and ODS TAGSETS.EXCELXP creates XML files. ODS creates ASCII text files that Excel knows how to open and render. When you use ODS methods, using .XLS as the file extension only "fools" the Windows registry into launching Excel when the file opens.

  The only way to create true, binary, proprietary Microsoft Excel files in proprietary Microsoft format is to use PROC EXPORT or the LIBNAME engine for Excel. In this case, you do not have any cosmetics (colors, fonts, titles, etc) because you are in "data-to-data" export mode using these methods.

cynthia

  BTW, XML is a valid file extension for Excel. So if you name the file with .XML (instead of .XLS), you will NOT get the Microsoft message on file open anymore.

View solution in original post


All Replies
Super Contributor
Posts: 273

Re: ODS- Saving Excel file

This was already discussed in

https : // communities . sas . com / message/40494#40494

Andre

Occasional Contributor
Posts: 9

Re: ODS- Saving Excel file


Hi Andre,

Thanks for the link. I just have a query.

Is there any other way to create Excel files directly?  I do not want HTML output to be saved in XLS format as  i need to change to XML to make it work fine Smiley Sad

Solution
‎09-11-2012 01:18 PM
SAS Super FREQ
Posts: 8,740

Re: ODS- Saving Excel file

Hi:

  That message is Microsoft's way of letting you know that it wants you to use the "correct" file extension for output. So, technically, they are correct, ODS HTML creates HTML files and ODS TAGSETS.EXCELXP creates XML files. ODS creates ASCII text files that Excel knows how to open and render. When you use ODS methods, using .XLS as the file extension only "fools" the Windows registry into launching Excel when the file opens.

  The only way to create true, binary, proprietary Microsoft Excel files in proprietary Microsoft format is to use PROC EXPORT or the LIBNAME engine for Excel. In this case, you do not have any cosmetics (colors, fonts, titles, etc) because you are in "data-to-data" export mode using these methods.

cynthia

  BTW, XML is a valid file extension for Excel. So if you name the file with .XML (instead of .XLS), you will NOT get the Microsoft message on file open anymore.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 519 views
  • 3 likes
  • 3 in conversation