ODS EXCEL gives error for larger data set

[Dreamer]

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

[RW9]

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.

[Tim_SAS]

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.

[megganwalker]

How do you enter MEMSIZE into SAS?

[PhilC]

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.