ODS excel error

[Anuz]

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 ?  

[data_null__]

Did your program fail due to memory?

I believe ODS EXCEL has to have enough memory to load an entire  sheet.  

[Anuz]

@data_null__ Nope ...no memory issues at all 

[PaigeMiller]

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.

[Anuz]

@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. 

[Anuz]

@PaigeMiller 

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]

If you've determined the column, now you work on determining the row.
Use a similar approach, export the first half of your data and then the second half. If both cause the issue you have more than one row causing the issue, otherwise you keep doing that until you find the issue.

For company name, you can look at COMPRESS() to start removing characters that could be problematic. Look at the modifiers and see if you can start with removing punctuations and other symbols.

[Anuz]

@Reeza I used the compress - p modifier for punctuations and that seems to have done the trick, 

[Reeza]

Or look for a foreign company, the accents or symbols on their name could cause the issue.

[Ksharp]

Did you try PROC EXPORT ?

This issue usually occurred under low version SAS (  < SAS9.4M4 ) .

[Anuz]

@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) . 

[ocean65]

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.

[ShannowZA]

A work-around is to insert a space after the + so that is not seen as a formula in excel....col1 = tranwrd(col1, '+', '+ ');