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.
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.
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.
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/
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?
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).
You have to ask the almighty overlords. They are immune to reason and arguments.
Libname XLSX should work (not excel)
What version of SAS are you using?
@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?
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.