BookmarkSubscribeRSS Feed
Reeza
Super User

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.

3 REPLIES 3
Domenico
Fluorite | Level 6

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

Reeza
Super User

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

Domenico
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1655 views
  • 0 likes
  • 2 in conversation