topic ODS- Saving Excel file in ODS and Base Reporting

ODS- Saving Excel file

KarthikR — Tue, 11 Sep 2012 09:26:47 GMT

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

Re: ODS- Saving Excel file

Andre — Tue, 11 Sep 2012 09:41:14 GMT

This was already discussed in

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

Andre

Re: ODS- Saving Excel file

KarthikR — Tue, 11 Sep 2012 09:53:44 GMT

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

Re: ODS- Saving Excel file

Cynthia_sas — Tue, 11 Sep 2012 17:18:35 GMT

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.