topic Out of memory while writing to Excel file in ODS and Base Reporting

Out of memory while writing to Excel file

WouterKBC — Wed, 26 Oct 2016 14:33:52 GMT

I'm using trying to export ~35000 positions to an Excel sheet with 'ods excel file'.

After a while, I get this error:

Error: Not enough memory to SET/GET instance attribute OA_RAW_VALUE(2660) for "SCRIPT.TCELL".

The whole process takes up more than 2GiB. Is there a way to solve this issue without increasing memory?

Thanks.

Re: Out of memory while writing to Excel file

RW9 — Wed, 26 Oct 2016 14:41:01 GMT

More importantly, do you believe Excel is the best store for 35k observations? That will create a vast file, depending on number of variables - not sure what positions are. Have you tried/can you - libname excel, and copy the data that way, or use proc export which can create an xls file - as its binary they tend to be far smaller. But really Excel isn't the place for that many observations, is this for a data transfer, use a file format which is good for that task, e.g. CSV, or delimited.

Proc export is probably the way to go if you really have to use Excel.

Re: Out of memory while writing to Excel file

WouterKBC — Wed, 26 Oct 2016 14:44:19 GMT

I'm using

proc print data

How would proc export work?

I've tried libname excel, but it gives me

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

Re: Out of memory while writing to Excel file

RW9 — Wed, 26 Oct 2016 14:48:34 GMT

For XLSX native files - these are actually XML files in a ZIP file, so the size is far larger than binary XLS:

http://blogs.sas.com/content/sasdummy/2012/01/25/export-to-excel-got-easier/

For XLS its similar, you change the dbms value to XLS.

Chris goes over some of the methods here:

http://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/

Re: Out of memory while writing to Excel file

WouterKBC — Wed, 26 Oct 2016 15:01:24 GMT

I have to work with XLSX.

The link you gave works, but in my previous code I had something like

sum cnt / style={tagattr='Format:#,##0'};

Which caluclated the sum of this column. Is this possible in this method?

Re: Out of memory while writing to Excel file

RW9 — Wed, 26 Oct 2016 15:06:16 GMT

Yes. Why do you have to work with XLSX? You have already limited yourself by using Excel, maybe you should only use cell B16 (joking, but seriously, use the best tool for the job).

Re: Out of memory while writing to Excel file

WouterKBC — Wed, 26 Oct 2016 15:08:34 GMT

You have to ask the almighty overlords. They are immune to reason and arguments.

Re: Out of memory while writing to Excel file

Reeza — Wed, 26 Oct 2016 18:29:29 GMT

Libname XLSX should work (not excel)

What version of SAS are you using?

Re: Out of memory while writing to Excel file

ballardw — Wed, 26 Oct 2016 21:49:02 GMT

@WouterKBC wrote:

You have to ask the almighty overlords. They are immune to reason and arguments.

If the result is as they desire do they need to know what you may have done in the middle?

Re: Out of memory while writing to Excel file

WouterKBC — Thu, 27 Oct 2016 11:36:28 GMT

If the result is as they desire do they need to know what you may have done in the middle?

They don't. So you suggest making an xls file and then convert it to xlsx? How do I do that?