BookmarkSubscribeRSS Feed
Dreamer
Obsidian | Level 7

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!!

9 REPLIES 9
ballardw
Super User

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.

Dreamer
Obsidian | Level 7

Thanks @ballardw, I'm already using "ods _all_ close;"

Dreamer
Obsidian | Level 7

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!!

ballardw
Super User

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.

Dreamer
Obsidian | Level 7

Yes I agree but business users don't understand this. They get a message that "this file is not in Excel format". For them Excel means "XLSX" Smiley Happy

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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:

https://support.microsoft.com/en-us/kb/948615

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
  • 6382 views
  • 0 likes
  • 5 in conversation