topic Re: ODS EXCEL gives error for larger data set in ODS and Base Reporting

ODS EXCEL gives error for larger data set

Dreamer — Thu, 22 Jan 2015 15:09:22 GMT

Hi,

I'm using "ODS EXCEL" to output "PROC REPORT" output to Excel 2007. It works fine when I have smaller data set (30 K Obs).

But it gives following error when my data set grows to 69K.

ERROR: Not enough memory to SET/GET instance attribute OA_UNFORMATTEDVALUE(3357) for "SCRIPT.TCELL".

ERROR: Event Stack Underflow. This is probably caused by mis-matched begin and end event calls.

ERROR: Fatal ODS error has occurred. Unable to continue processing this output destination.

ERROR: Event Stack Underflow. This is probably caused by mis-matched begin and end event calls.

ERROR: In event 'doc': ?:0: attempt to get length of field 'line' (a nil value)

stack traceback:

?: in function 'put'

?: in function 'puts'

?: in function 'put'

?: in function 'finish_worksheet'

?: in function '?'

?: in function <?:491>

(tail call): ?

: in function 'new_document'

?: in function 'new_stream'

?: in function 'finish_worksheet'

?: in function '?'

?: in function <?:491>

(tail call): ?

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

I have attached log of this run. PFA

Did any one face this issue? Any suggestion??

Re: ODS EXCEL gives error for larger data set

RW9 — Thu, 22 Jan 2015 15:19:32 GMT

Excel has limitations on the number of rows/columns you can put into it. That is why you are getting these problems. Excel is not a tool for <insert what you intend to do with it>. Even if you are putting data out to it for people to look at, no-one in their right mind is going look at that much data.

If you are using this for data transfer, then use a proper data transfer format - delimited (e.g. csv), or xml.

Re: ODS EXCEL gives error for larger data set

Tim_SAS — Thu, 22 Jan 2015 15:33:51 GMT

You should report this to Tech. Support. They'll probably want to see your program and data set (or a data set that is similar enough to reproduce the problem).

In the meantime, try increasing the amount of memory available to SAS by using the MEMSIZE option. Typically what I do in cases like this is use both the MEMSIZE MAX and FULLSTIMER options. The FULLSTIMER option adds a message to the log that includes the amount of memory SAS actually used. Then you can specify that amount (or a little extra) the next time you run your program.

You can submit a problem report by clicking the submit a problem report link at the bottom of this page.

Re: ODS EXCEL gives error for larger data set

megganwalker — Tue, 21 Jan 2020 17:45:39 GMT

How do you enter MEMSIZE into SAS?

Re: ODS EXCEL gives error for larger data set

PhilC — Tue, 10 Mar 2020 13:37:58 GMT

I just had similar errors, involved with ODS Excel, Proc Print, and a large dataset. It turned out to be a picture format that once eliminated from the code stopped the errors. To whomever it may concern in the future.