When you are exporting to Excel or Access tables, the following error messages can appear:

   ERROR: Error attempting to CREATE a DBMS table.
   ERROR: Execute: Too many fields defined...
   WARNING: File deletion failed for _IMEX_.(dsname).DATA
   ERROR: Export unsuccessful.  See SAS Log for details.

These messages can appear when you attempt the following type of exports:

1. Create a sheet
2. Replace the sheet with more fields
3. Replace the sheet with less fields
4. Replace the sheet with more fields again

The problem is due to a bug in the Microsoft Jet Database Engine. During PROC EXPORT, the Jet provider creates two tables: one as a named range and one as a full 255-field/column worksheet.

When PROC EXPORT - Replace is executed, the worksheet table, which has 255 fields, is not removed - only the Named Range is removed. So, when you add or modify a new field, the Jet maximum column limit is exceeded.

For more information on this error see the Microsoft Knowledgebase article Q128221

To resolve the problem, manually delete the sheet before exporting to it or you can use PROC SQL to delete the sheet first as shown below. In this case the name of worksheet is air1.

libname myexcel excel 'j:\access\accpcff\excelfiles\air.xls';
proc sql;
drop table myexcel.air1;
run;
libname myexcel clear;

proc export data=sashelp.air outfile='j:\access\accpcff\excelfiles\air.xls' dbms=excel replace;
sheet='air1';
run; quit;
 

Operating System and Release Information