The SAS Output Delivery System and reporting techniques

ODS Proc Report - performance issue

Reply
Contributor
Posts: 22

ODS Proc Report - performance issue

Hi Experts,

 

I use SAS EG 7.1. I want to export reports to excel as follows:

 

I want to export a report as .xlsx. For this purpose, I am using ODS EXCEL and PROC REPORT. This is working fine with small files say if a dataset has rows upto # 5,000 and output gets generated in few seconds.

 

But if the # of rows exceed 5,000 in the report, it takes more time to run and after 1 min or so a message pops up “The results are larger (#### bytes) and could take long time and a large amount of system resources to add to the project”. Then I click ‘Yes’. Then it produces output.

 

But if the # rows exceed 20,000, after I click ‘Yes’, it also closes SAS session.

 

What can I do to so it write to excel without any problem. And I don’t want user to see that message and click Yes or No.

 

I would appreciate your help.

Super User
Super User
Posts: 7,668

Re: ODS Proc Report - performance issue

For what pupose do you want to write out 20,000 records to an XML tagset?  It may be possible, but noone is ever going to look at data of the quantity, and if it is to send to anyone who will actually program on that data you would be far better of with CSV data, as its small and easier to work with.

 

From your error, it looks like your are just running out of resources, check with the people who installed your EG, they may be able to expand your work area or resources, or as I said above, use a file format appropriate to the task in hand (and Excel is never that appropriate format)

Super User
Posts: 3,233

Re: ODS Proc Report - performance issue

Using ODS for large file exports is not recommended as it is very resource intensive.

 

PROC EXPORT or using a  LIBNAME with the EXCEL engine are better options in this case.

Contributor
Posts: 22

Re: ODS Proc Report - performance issue

The reason I am using ODS because I need to format columns in dollars and commas. Also I want to adjust width of the cells and add titles to the excel output.

 

When I tried Proc export, I am losing all the formats.. and there is no way I can add title or adjust widths. Or is there a way?

Super User
Super User
Posts: 7,668

Re: ODS Proc Report - performance issue

Sorry, this goes back to my question, why do you want to create a formatted report (i.e. adding non-essential things like percent symbol) to data which is 20k rows long.  No-one will every look at a data report like that.  What is the purpose of this document?

Contributor
Posts: 22

Re: ODS Proc Report - performance issue

This is a report request. Also it’s a summary report at 3 level of groupings(procedure codes)..that’s why it’s big.

Super User
Super User
Posts: 7,668

Re: ODS Proc Report - performance issue

So someone is actually going to sit down and look through 20k rows of data?  

 

The best way I can think of doing it if you really have to, is to export to CSV, and then have an Excel file with a VBA macro which formats the data as you want.  The file is still going to be huge though, and use a fair chunk of resource.  Alternatively why don't the people who are going to review this data get Office Add-in from SAS, they can then view SAS dataset directly through Excel.

Super User
Posts: 11,101

Re: ODS Proc Report - performance issue

If you do not have an excessive number of COLUMNS you might try ODS RTF

And possibly consider some PAGE variables in those grouping variables if not BY groups.

 

Though I really don't know about actual need of $ and % in the body of tables. I generally use column or row headings to indicate that as the body of large tables gets too busy.

Contributor
Posts: 22

Re: ODS Proc Report - performance issue

I got response from SAS Technical help, it works like magic for this partuclar project. Here is the solution:

 

Tools > Options > Results > Results General, then you will see the option 'Prompt before opening results larger than:" then specify some value.

In addition, on the same page, you might want to  un-select the option "Automatically open data or results when generated".

Ask a Question
Discussion stats
  • 8 replies
  • 568 views
  • 0 likes
  • 4 in conversation