ODS tagsets error while reading excel file by using Enterprise Guide

[Cherry]

Hi ,

Please help us on the following issue.

Below is the SAS program and log when I output a dataset to excel file, the dataset is named FCLS1, which only contains 1 fields with 37296 records.

SAS program:
ods tagsets.excelxp
options(
frozen_headers='Yes' autofilter='All'
embedded_titles='No' replace = 'Yes'
)

file="/home/z60640/EG_TEST/sample.xls" style=journal;
ods noproctitle;
proc print data=FCLS1;
run;
ods tagsets.excelxp close;

SAS Log:
***********************************************Log start******************************************************
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %_eg_hidenotesandsource;
18
19 ods tagsets.excelxp
20 options(
21 frozen_headers='Yes' autofilter='All'
22 embedded_titles='No' replace = 'Yes'
23 )
24
25 file="/home/z60640/EG_TEST/sample.xls" style=journal;
NOTE: Writing TAGSETS.EXCELXP Body file: /home/z60640/EG_TEST/sample.xls
Unrecognized option: REPLACE
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.
26 ods noproctitle;
27 proc print data=FCLS1;
28 run;

NOTE: There were 37296 observations read from the data set WORK.FCLS1.
NOTE: PROCEDURE PRINT used (Total process time):
real time 47.05 seconds
cpu time 43.68 seconds


29 ods tagsets.excelxp close;
ERROR: Undetermined I/O failure.
30
31 %_eg_hidenotesandsource;
43
44
45 QUIT; RUN;
46
***********************************************Log end******************************************************

[Cynthia_sas]

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

[boschy]

Cynthia covered the main points.

I'll only add that, traditionally, the /home disk on a UNIX or LINUX server usually has a small amount of space allocated to it. Possibly this area filled up during the PROC PRINT.

But a write permission problem seems more likely, since I think you'd still get a partial XLS file if the disk filled up. This behaviour will be O/S platform dependent.