An error can occur when you export large volumes of data from the SAS® System to Microsoft Excel 2007 workbooks


When you export a large volume of data to Excel 2007 workbooks, you can receive the following error:

NOTE: File "c:\sastest\testexport.xlsx" will be created if the export process succeeds. 
ERROR: Execute: Unable to insert row
WARNING: File deletion failed for _IMEX_.Data.DATA.
ERROR: Export unsuccessful. See SAS Log for details.

The error occurs when you run an EXPORT procedure similar to the following:

proc export data= x outfile="c:\sastest\testexport.xlsx" dbms=excel replace;
sheet="Data";
newfile=yes;
run;

The problem occurs because the SAS System uses the Microsoft Access Connectivity Engine(ACE) to create these files. When you use large files that exceed approximately 50,000,000 cells, the Access Connectivity Engine uses more memory than a single Windows process has available.

To circumvent the problem, you should divide the data across multiple sheets. Below is a program that you can use to split the data. You can set the program as high as the number of rows the machine's memory allows.

 

****************************************************************;
*program:  datasplit2excel.sas                                 *;
*purpose:  to split any dataset across multiple spreadsheets   *;
*          with the number of rows you specify                 *;
*date:     Feb 2005                                            *;
*restrictions:  only restricted by the number of sheets        *;
*               excel can create                               *;
****************************************************************;

 

options mprint macrogen symbolgen;

%macro splitter(libname,libref,dsname,sheetname,xlsname,dbms,nrows,replaceit,dblabel);

dm wpgm 'clear log' wpgm;

libname &libref &libname;

data multexp.codetest;
do x = 1 to 250000;
y = ranuni(x);
output; end; run;
*****************************************************************;
* this little routine is used if the customer wants to wipe out *;
* the .xls file before creating a new .xls file                 *;
* V9 by default appends new sheets to the existing .xls file    *;
* rather than wiping out the sheet, we compress the quotes      *;
* so that systask can work                                      *;
*****************************************************************;

data _null_;
xlsname=compress(&xlsname,'"');
call symput('xlsname2',xlsname);
run;


%if &replaceit=1 %then %do;
data _null_;
systask command "del &xlsname2";
run;

%end;

****************************************************************************;
*this routine takes the original dataset and splits it based on the number *;
*of rows specified at macro invocation.  the default is 65535              *;
*which is the maximum number of rows excel can handle                      *;
****************************************************************************;

data byvals; 
set &libref..&dsname;
bygrp=int(((_n_/&nrows)+1)-.00000001);
sheetname=cats("&sheetname",put(bygrp,8.));
run;

proc sort data=byvals nodupkey out=setup (keep= bygrp sheetname) ;
by descending bygrp;
run;

**************************************************;
*this routine tells me how many groups there are *;
**************************************************;

data _null_;
set setup end=last;
call symputx('byval'||left(put(_n_,8.)),sheetname);
if last then call symputx('counter',put(_n_,8.));
run; 

*****************************************************;
* we then create the subset sas datasets for export *;
*****************************************************;
libname xcl &xlsname;

data
%do i=1 %to &counter;
 &&byval&i (drop=bygrp sheetname)
 %end;
;
set byvals;
%do i=1 %to &counter;
if sheetname="&&byval&i" then output &&byval&i;
%end;
run;

***************************************************************;
* setup the libname and datastep here with the dblabel option *;
***************************************************************;

%do i= &counter %to 1 %by -1;
data xcl.&&byval&i (dblabel=&dblabel);
set &&byval&i;
run;

%end;

libname xcl clear;

%mend splitter;

%splitter('K:\myData',                    /* location of data library */
           multexp,                       /* libref */
           codetest,                      /* sas dataset to export */
           sheet,                         /* name of the sheets you desire  */
           "c:\testsas\mysplit.xls",      /* name of the .xls file created or appended */
           excel,                         /* dbms to use                               */
           65535,                         /* number of rows to fill each sheet(65535 is max) */
           1,                             /* replace existing xls entirely 1 is yes 0 is no */
           no);                           /* dblabel yes or no */