BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KarthikR
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
Diamond | Level 26

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

3 REPLIES 3
Andre
Obsidian | Level 7

This was already discussed in

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

Andre

KarthikR
Calcite | Level 5


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

Cynthia_sas
Diamond | Level 26

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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