Hi all,
I do an ods excel of a dataset with about 25000 records and 50 columns.
I have been getting an error that says "We found a problem with some content. Do you want us to try to recover as much as we can ? " I click yes.
Then it says Excel was able to to open the file by repairing or removing the unreadable content. Removed Records : Formula from /xl/worksheet/sheet1.xml part "
I tried all sorts of permutations and combinations to find the offending data column.
I tried only outputting some rows ( that worked fine) . Then I started excluding some columns to find if that is the column causing the error. On the face of it it didn't look like any records were removed.
Finally I think I have narrowed down the column causing the issue to a Character column. I took that column out from the ods and all worked ok. So there must be some offending characters or something in that column.
It's a Company name column so could be that it has some odd characters that excel may not like. It is difficult to find which row entry might be causing the issue.
How can I clean up that column so my user can open the file without the errors please ?
Did your program fail due to memory?
I believe ODS EXCEL has to have enough memory to load an entire sheet.
@data_null__ Nope ...no memory issues at all
There are probably errors in the SAS log. You need to fix these.
If you're not sure how to fix the errors, then please show us the LOG for the ODS Excel part.
@PaigeMiller ok. I hadn't noticed anything in the log in terms of errors. But maybe I overlooked or missed any messages that might have given an indication. Let me rerun with the column back in and share.
There are no errors. I tried running the ods without the offending column and have the same log. Only thing is the warning which I have sorted using goptions device=actximg;
25 GOPTIONS ACCESSIBLE;
26
27 ods excel file="&rpt_path./TEST_COMP.xlsx"
28 options( sheet_name="Comp Data");
WARNING: Unsupported device 'ACTIVEX' for EXCEL destination. Using device 'ACTXIMG'.
29 options validvarname=UPCASE label=0;
30
31 proc report data= RPT_TBL.COMP_TABLE ;
32 &title_gen.;
33 title2 underlin=1 font="Times New Roman" justify=left height=2.5 color=crimson bold "Test Company-&date.";
34 run;
NOTE: There were 25324 observations read from the data set RPT_TBL.COMP_TEST
NOTE: PROCEDURE REPORT used (Total process time):
real time 25:33.16
cpu time 25:13.75
35 ods excel close;
NOTE: Writing EXCEL file: /data1/general/FILES/ALL_PERI/XL_REPORTS/TEST_COMP.xlsx
@Reeza I used the compress - p modifier for punctuations and that seems to have done the trick,
Did you try PROC EXPORT ?
This issue usually occurred under low version SAS ( < SAS9.4M4 ) .
@Ksharp yes. I did with proc export. it doesnt have that issue.
When i used the compress with modifier - p that seems to have resolved the issue (for ods) .
Hi Anuz,
I got this error message, too.
The cause in my case were calculation signs (+ - > <) at the beginning of the cell.
Obviously ODS Excel cannot handle this.
After replacing the calculation signs by words (plus, minus, gt, lt), it worked.
A work-around is to insert a space after the + so that is not seen as a formula in excel....col1 = tranwrd(col1, '+', '+ ');
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
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.
Ready to level-up your skills? Choose your own adventure.