Hi:
I believe you will have to work with Tech Support on this issue, but here is some background information that may help you understand some of the issues.
-- If you closely examine the SAS log you will see this message:
Unrecognized option: REPLACE
which is causing at least one issue.
-- if you submit the following code:
[pre]
ods _all_ close;
ods tagsets.excelxp file='mystuff.xls' options(doc='Help');
proc print data=sashelp.class(obs=1);
run;
ods tagsets.excelxp close;
[/pre]
Then I would urge you to do 2 things.
1) look in the SAS log -- all the valid suboptions that you can use will be listed in the SAS log from the doc='Help' suboption usage. Also, this paper is a good overview of what you can accomplish with suboptions:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf
2) exit from EG, find the file: mystuff.xls and open the file with NOTEPAD or any text editor. Do not double click on it. Do not open the file with Excel. Open the file with Notepad. Look at the tags in the file -- the tags will be delimited by < and >
The reason for this #2 view of the file with Notepad is to show you that you are NOT creating a true, binary Excel file when you use TAGSETS.EXCELXP to create output. You are not technically outputting your dataset to a true, binary Excel proprietary format file.
ODS TAGSETS.EXCELXP conforms to the Microsoft Office 2002/2003 specification for Spreadsheet Markup Language XML -- and you are only "fooling" the Windows registry into opening the file when you double click on the file name by naming it with .XLS.
Since TAGSETS.EXCELXP is creating an XML file, you must use the suboptions that put valid XML instructions into the file (such as for autofilter and embedded titles, etc). When you use PROC EXPORT, for example, there is support for the REPLACE option however, TAGSETS.EXCELXP is NOT doing an export of the data -- if you used PROC EXPORT, you would be creating a true, binary Excel file, but you weould not be able to control frozen headers in the output. TAGSETS.EXCELXP has featured that go above and beyond just getting the data from SAS into Excel. TAGSETS.EXCELXP allows you to provide controls that are only available in the XML.
-- The location of your output file looks like a UNIX path. Are you sure that you (or your logon id) has write permission to that location?? When you are writing a large number of records, are you sure that there are no time out limits that you are running into??? The other message in your file, that is relevant is:
ERROR: Undetermined I/O failure.
This Tech Support note, seems relevant to your question:
http://support.sas.com/kb/6/890.html
As a test, in a Windows system, with SAS 9.2, I created a really big file with only one variable and a LOT of rows (380000) -- and SAS created the file and Excel (2010) opened the XML file without problems. However it did take over 2 minutes for the XML file to be created (with an extension of .XLS).
[pre]
8 data new(keep=newvar);
9 set sashelp.class;
10 newvar = catx('~',name,sex,age,height,weight);
11 do i = 1 to 20000;
12 output;
13 end;
14 run;
NOTE: There were 19 observations read from the data set
SASHELP.CLASS.
NOTE: The data set WORK.NEW has 380000 observations and 1
variables.
NOTE: DATA statement used (Total process time):
real time 0.31 seconds
cpu time 0.23 seconds
15 ods tagsets.excelxp file='c:\temp\bigmystuff.xls';
NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\bigmystuff.xls
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3
and above, v1.94, 09/09/12). Add options(doc='help') to the ods
statement for more information.
16 proc print data=new;
17 run;
NOTE: There were 380000 observations read from the data set
WORK.NEW.
NOTE: PROCEDURE PRINT used (Total process time):
real time 2:53.87
cpu time 2:53.83
18 ods tagsets.excelxp close;
[/pre]
This shows that TAGSETS.EXCELXP can write an XML file with a LOT of records. So there must be other factors at work in your scenario. I would recommend that you double check the network path name and write permissions and possibly time out limits and/or work with Tech Support on this question.
cynthia