05-06-2015 02:52 PM
I'm using SAS 9.4 and office 2007 to generate an Excel report using "Ods Excel".
But I'm getting following message in SAS log:
ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 89225 observations read from the data set WORK.DSName.
NOTE: PROCEDURE REPORT used (Total process time):
real time 29.10 seconds
cpu time 28.34 seconds
Following is my code:
ods _all_ close;
options(sheet_interval='none' sheet_name='SheetName' sheet_label=' ' suppress_bylines='yes'
orientation='landscape' fittopage='yes' embedded_titles='yes' autofit_height='yes' center_horizontal='yes'
Proc report ........
ODS Excel close;
Can anyone please suggest me how to debug and fix this kind of issues?
Thanks in advance!!
05-06-2015 03:19 PM
Can you run the Proc Report part without the ODS and finish? If not you might want to close the HTML destination before hand (ODS HTML Close). The system may run out of resources as it tries to generate a long complex html table in memory as well as the ODS destination.
05-06-2015 04:05 PM
My report have 52 columns and It looks like ODS Excel doesn't support report with more than 36 columns.
So now I'm using ODS tagset and creating xml output. At least this way now I can open this file in Excel with all formatting.
This works fine for me except that its not a true Excel document!!
05-06-2015 04:09 PM
As I understand it ODS Excel is experimental. So finding limits and undesired behavior (which is my experience with Excel in general) is not unexpected.
05-07-2015 03:13 AM
XLSX is nothing more than compressed XML. So no real difference to the XML created by tagsets.excelxp. Which has the nice advantage of working.
05-07-2015 01:03 PM
05-08-2015 04:48 AM
If you specify a filename extension of .xml, Excel does not complain on opening. Just RMB and "open with Excel"
05-08-2015 05:05 AM
You can also associate the filetype of XML with Excel, by doing the right click open with and selecting the check box next to Always open with this program.
Although from my side, if your users are going to use the file, or any other file, then they should be aware of this thing anyway as this is built in functionality in Excel. File type is merely an indicator to the operating system as to what program to open the file with, in most user systems, file extension is hidden for known file types. If they really can't handle it then have a look at the M$ help page for a registry fix: