ERROR: The MS Excel table (worksheetname) has been opened for OUTPUT.

I happend to stumbleupon a post from SAS support blog regarding the ERROR message in the LOG file when trying to output a SAS dataset in the form of Excel sheet.


Direct link:


ERROR: The MS Excel table (worksheetname) has been opened for OUTPUT.

This table already exists, or there is a name conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option.
ERROR: Export unsuccessful. See SAS Log for details.

When you use the EXPORT procedure on an Excel workbook, the workbook might be corrupted and the following error message generated:

This problem can occur if a previous EXPORT procedure attempts to export a SAS data set in the workbook that does not contain any observations. The following example illustrates an export procedure on such a data set:

%macro blowup;

data a;
a=1;
stop;
run;


%do i=1 %to 2;

proc export data=a outfile="c:\temp\test.xls"
dbms=excel2000 replace;
run;


%end;
%mend;
%blowup;

The problem occurs because the SAS data set does not contain any data to export. As a result, a corrupted structure is created.

To circumvent the problem, do one of the following:

•Use the SQL procedure with a DROP TABLE statement to drop the empty data set before replacing it, as shown in the following example:

%macro blowup;

data a;
a=1;
stop;
run;


%do i=1 %to 2;

libname test excel 'c:\sastest\test2.xls';


proc sql;
drop table test.a;
quit;


libname test clear;


proc export data=a outfile="c:\sastest\test2.xls"
dbms=excel2000 replace;
run;


%end;
%mend;
%blowup;