The SAS Output Delivery System and reporting techniques

Out of memory while writing to Excel file

Reply
Contributor
Posts: 28

Out of memory while writing to Excel file

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.

Super User
Super User
Posts: 7,997

Re: Out of memory while writing to Excel file

Posted in reply to WouterKBC

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.

Contributor
Posts: 28

Re: Out of memory while writing to Excel file

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.
Super User
Super User
Posts: 7,997

Re: Out of memory while writing to Excel file

Posted in reply to WouterKBC

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/

Contributor
Posts: 28

Re: Out of memory while writing to Excel file

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?

 

Super User
Super User
Posts: 7,997

Re: Out of memory while writing to Excel file

Posted in reply to WouterKBC

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).

Contributor
Posts: 28

Re: Out of memory while writing to Excel file

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

Super User
Posts: 19,878

Re: Out of memory while writing to Excel file

Posted in reply to WouterKBC

Libname XLSX should work (not excel) 

 

What version of SAS are you using? 

Super User
Posts: 11,343

Re: Out of memory while writing to Excel file

Posted in reply to WouterKBC

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?

Contributor
Posts: 28

Re: Out of memory while writing to Excel file

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?

Ask a Question
Discussion stats
  • 9 replies
  • 738 views
  • 1 like
  • 4 in conversation