topic Re: ERROR: The SAS System stopped processing this step because of insufficient memory. in ODS and Base Reporting

ERROR: The SAS System stopped processing this step because of insufficient memory.

Dreamer — Wed, 06 May 2015 18:52:47 GMT

Hi,

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;

/*Create Report*/

ods escapechar="^";

options missing='';

options device=ACTXIMG;

ods excel

file=&outfile.

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'

embedded_footnotes='yes')

style=minimal ;

Proc report ........

run;

quit;

ODS Excel close;

Can anyone please suggest me how to debug and fix this kind of issues?

Thanks in advance!!

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

ballardw — Wed, 06 May 2015 19:19:03 GMT

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.

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

Dreamer — Wed, 06 May 2015 20:02:07 GMT

Thanks @ballardw, I'm already using "ods _all_ close;"

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

Dreamer — Wed, 06 May 2015 20:05:56 GMT

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!!

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

ballardw — Wed, 06 May 2015 20:09:49 GMT

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.

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

Kurt_Bremser — Thu, 07 May 2015 07:13:42 GMT

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.

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

Dreamer — Thu, 07 May 2015 17:03:45 GMT

Yes I agree but business users don't understand this. They get a message that "this file is not in Excel format". For them Excel means "XLSX"

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

Reeza — Thu, 07 May 2015 17:11:39 GMT

This may be useful for you:

43496 - Convert files created using an ODS destination to native Excel files

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

Kurt_Bremser — Fri, 08 May 2015 08:48:43 GMT

If you specify a filename extension of .xml, Excel does not complain on opening. Just RMB and "open with Excel"

Re: ERROR: The SAS System stopped processing this step because of insufficient memory.

RW9 — Fri, 08 May 2015 09:05:33 GMT

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:

https://support.microsoft.com/en-us/kb/948615