Hi,
I'm using SAS 9.4 and office 2007 to generate an Excel report using "Ods Excel".
But I'm getting following message in SAS log:
ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 89225 observations read from the data set WORK.DSName.
NOTE: PROCEDURE REPORT used (Total process time):
real time 29.10 seconds
cpu time 28.34 seconds
Following is my code:
ods _all_ close;
/*Create Report*/
ods escapechar="^";
options missing='';
options device=ACTXIMG;
ods excel
file=&outfile.
options(sheet_interval='none' sheet_name='SheetName' sheet_label=' ' suppress_bylines='yes'
orientation='landscape' fittopage='yes' embedded_titles='yes' autofit_height='yes' center_horizontal='yes'
embedded_footnotes='yes')
style=minimal ;
<<
Proc report ........
>>
run;
quit;
ODS Excel close;
Can anyone please suggest me how to debug and fix this kind of issues?
Thanks in advance!!
Can you run the Proc Report part without the ODS and finish? If not you might want to close the HTML destination before hand (ODS HTML Close). The system may run out of resources as it tries to generate a long complex html table in memory as well as the ODS destination.
Thanks @ballardw, I'm already using "ods _all_ close;"
My report have 52 columns and It looks like ODS Excel doesn't support report with more than 36 columns.
So now I'm using ODS tagset and creating xml output. At least this way now I can open this file in Excel with all formatting.
This works fine for me except that its not a true Excel document!!
As I understand it ODS Excel is experimental. So finding limits and undesired behavior (which is my experience with Excel in general) is not unexpected.
XLSX is nothing more than compressed XML. So no real difference to the XML created by tagsets.excelxp. Which has the nice advantage of working.
This may be useful for you:
43496 - Convert files created using an ODS destination to native Excel files
If you specify a filename extension of .xml, Excel does not complain on opening. Just RMB and "open with Excel"
You can also associate the filetype of XML with Excel, by doing the right click open with and selecting the check box next to Always open with this program.
Although from my side, if your users are going to use the file, or any other file, then they should be aware of this thing anyway as this is built in functionality in Excel. File type is merely an indicator to the operating system as to what program to open the file with, in most user systems, file extension is hidden for known file types. If they really can't handle it then have a look at the M$ help page for a registry fix:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.