BookmarkSubscribeRSS Feed
WouterKBC
Obsidian | Level 7

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.

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

WouterKBC
Obsidian | Level 7

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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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/

WouterKBC
Obsidian | Level 7

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?

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

WouterKBC
Obsidian | Level 7

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

Reeza
Super User

Libname XLSX should work (not excel) 

 

What version of SAS are you using? 

ballardw
Super User

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

WouterKBC
Obsidian | Level 7

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5680 views
  • 1 like
  • 4 in conversation