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

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

Reeza — Thu, 17 Jan 2013 20:25:50 GMT

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 a 64-bit Excel driver

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.

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

Domenico — Mon, 21 Jan 2013 14:06:33 GMT

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

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

Reeza — Mon, 21 Jan 2013 16:50:42 GMT

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

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

Domenico — Mon, 21 Jan 2013 16:59:39 GMT

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