DATA Step, Macro, Functions and more

SAS 9.2 and Excel 2010 - change in formats or unreadable error

Reply
Super User
Posts: 17,823

SAS 9.2 and Excel 2010 - change in formats or unreadable error

What I'm trying to do:

I have an Excel report built with the data sources on a separate sheet that feeds pre formatted tables and graphs.  The template is saved as a .XLS file.

When I export data to it from SAS 9.2 using the libname engine (excel) then the formatting changes, e.g. colours and styles, some things go bold and others change colours. 

I tried saving the file as .xlsx and then writing to it that way, there are no more format changes but I get an error:

Excel found unreadable content" error in .xlsx file


I found this usage note on how to get rid of it but not sure it applies.


40383 - An unreadable content error occurs when you open Microsoft Excel files that were created by ...


Has anyone come across this issue before and is there a workaround/solution. 


There doesn't appear to be any actual unreadable content that I could find and the reports work fine except for the error.

Occasional Contributor
Posts: 10

Re: SAS 9.2 and Excel 2010 - change in formats or unreadable error

Hi Reeza,

i got this problem one week ago. I could resolve it by changing the statement option from dbms=xls to dbms=excelcs.

By the way keep .xls not .xlsx!!

best regards

Domenico

Super User
Posts: 17,823

Re: SAS 9.2 and Excel 2010 - change in formats or unreadable error

ExcelCS doesn't allow me to drop my tables and/or rewrite over them though.

Occasional Contributor
Posts: 10

Re: SAS 9.2 and Excel 2010 - change in formats or unreadable error

Did you add the replace statement?

I'm using SAS 9.2 M3 and Windows7 Office2010 and the code below works for me very fine.

proc export data=work.datahave

    outfile="datahave.xls"

    dbms=excelcs replace;

    sheet= "Datahave";

run;

I forgot to mention that if you have already an existing file which you have exported with the statement option dbms=xls, then you have to use a different filename for the first time. Afterwards it shouldn't be a problem to replace the file.
Good luck

Domenico

Ask a Question
Discussion stats
  • 3 replies
  • 870 views
  • 0 likes
  • 2 in conversation