BookmarkSubscribeRSS Feed
AnnaShami
Fluorite | Level 6

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.

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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)

SASKiwi
PROC Star

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.

AnnaShami
Fluorite | Level 6

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

AnnaShami
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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.

AnnaShami
Fluorite | Level 6

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

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
  • 8 replies
  • 2204 views
  • 1 like
  • 4 in conversation