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:
I found this usage note on how to get rid of it but not sure it applies.
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.
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
ExcelCS doesn't allow me to drop my tables and/or rewrite over them though.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.