Problem Note 34119: An error "Too many fields defined. . ." occurs when you export SAS® data sets to Microsoft Excel 2007 files
 |  |  |  |
When you export SAS data sets to Excel 2007 files, you can receive the following error:
proc export dbms=excel2007
outfile='c:\sastest\myexcel2.xlsx'
data=largevars replace;
sheet='bigsheet';
run;
outfile='c:\sastest\myexcel2.xlsx'
data=largevars replace;
sheet='bigsheet';
run;
ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: Too many fields defined..
WARNING: File deletion failed for _IMEX_.bigsheet.DATA.
ERROR: Export unsuccessful. See SAS Log for details.
WARNING: File deletion failed for _IMEX_.bigsheet.DATA.
ERROR: Export unsuccessful. See SAS Log for details.
The problem occurs when you export more than 255 columns of data to the Excel 2007 file. Excel 2007 has limits of 1,048,576 rows by 16,384 columns. PROC EXPORT uses the Microsoft Access Control Entry provider to export to these files, and that provider has a limit of 255 columns.
To circumvent the problem, use DBMS=XLSX rather than DBMS=EXCEL because it uses a different engine.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft® Windows® for x64 | 9.2 TS1M0 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS1M0 | |||
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS1M0 | |||
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS1M0 | |||
| Microsoft Windows XP Professional | 9.2 TS1M0 | |||
| Windows Vista | 9.2 TS1M0 | |||
Microsoft Excel 2007 files have a limit of 1,048,576 rows by 16,384 columns. The SAS IMPORT and EXPORT procedures use the Microsoft Access Control Entry provider. If you export more than 255 columns, PROC EXPORT fails with an error.
| Type: | Problem Note |
| Priority: | medium |
| Date Modified: | 2008-12-05 10:35:21 |
| Date Created: | 2008-12-04 13:16:40 |