ODS excel error: the number of columns to be exported?

[braam]

I've read a bunch of discussion regarding what is the best to export to excel. It seems to me that many people recommend one to use ODS export. Accordingly, I made a multiple column table that I would like to export to Excel, so I wanted to use ODS excel. Then I faced the following message when I tried to open the excel file that saved my table via ODS excel.

"We found a problem with some content in 'MY FILE NAME'. Do you want us to try to recover as much as we can? If you trust the source of this workbook click Yes".

Anyway I clicked "Yes" to see my table in the excel file. I noticed that my table is truncated at the column Y. If it went well, it would include columns from A to AA. I also confirmed that this issue doesn't arise when the original SAS set has fewer columns (so that it can be included in columns A through Y). Given that, I don't think the issue is related to the sas dataset, but it's related to the number of columns that my SAS can export to Excel. 

As an experiment, even if I give an ODS option to start writing from column G (or any column), the last column in Excel file is still Y. If I give an ODS option to start writing from column Y, the excel file includes just one column, Y. Does anybody know how to solve this issue? Thanks in advance.

%utlfkil("C:\Dropbox\temp20191012_2.xlsx"); * delete if exist;
ods excel file= "C:\Dropbox\temp20191012_2.xlsx";
ods excel options(sheet_name= "sn" sheet_interval= "none" start_at= "G1" ); /*start_at= "A1"; */ /*start_at= "Y1"; */
proc report data= R_comp; run;
ods EXCEL  close;
options noxsync noxwait; X "C:\Dropbox\temp20191012_2.xlsx";;

[Tom]

You usually see that type of error from Excel when no output was ever written to the ODS EXCEL file.

Check you log to see if the PROC actually wrote an pages of output.

[braam]

Thanks for your answer. My log shows that there is no problem. SAS results viewer provides results that it is supposed to display.

I tried using applianc dataset in sashelp library. It is also truncated in column Y.

%utlfkil("C:\Dropbox\temp20191012_2.xlsx"); * delete if exist;
ods excel file= "C:\Dropbox\temp20191012_2.xlsx";
data temp; set sashelp.applianc;
	var1= 1;
	var2=2;
	var3=3;
	run;

proc report data= temp; run;
ods EXCEL  close;
options noxsync noxwait; X "C:\Dropbox\temp20191012_2.xlsx";;
ods html;

As mentioned earlier, this issue happens only when there are columns beyond Y. If the table is small enough to end before column Y, it's OK.

[Tom]

Are you really trying to open it with Excel immediately after running the ODS EXCEL CLOSE statement?

Perhaps Excel is trying to read the file before SAS has finished writing it.

Try giving a little time to let SAS finish writing the file to the disk and closing it before trying to open it with Excel.

[braam]

Thanks. But I took enough time before I tried to open it. This actually doesn't matter. Again, what matters to me is whether the original SAS dataset I would like to report goes beyond column Y or not.

When I click "Yes" from the message mentioned in my post ("We found a problem with...."), the following message shows up:

"Excel was able to open the file by repairing or removing the unreadable content.
Removed Records: Cell information from /xl/worksheets.sheet1.xml part"

I found out that the issue is related to Excel program installed in my PC. I tried the same code in another PC, and it worked well. I'm still not sure what's the cause in the excel program. Based on the message I've got from Excel, please help me if anybody knows this issue. Thanks.