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
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.
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
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.