Improve the Performance of ODS EXCEL destination for large tables

[pchegoor]

Often times i  have had scenarios where the users needed to look at an Excel file having significant number rows ( > 100K)  and also columns ( > 40)  .  To create such a file, I  have tried tagsets but they all fell short of expectations of size and performance.  Since  ODS EXCEL produces native .xlsx binary files occupying much less size compared to all other methods I would like to propose that this Destination be improved to ensure large tables can also be exported to Excel via this method. Obviously, this Destination  right now does suffer from memory issues when dealing with large tables  as noted here 

 

http://support.sas.com/kb/56/985.html

Some workarounds suggested are splitting large tables etc but this not always welcome by the users.  I  currently use  Base SAS  9.4M4 and this issue still exists. Increasing MEMSIZE system option also does not always help.  I  do understand this is not an easy issue to address considering how the Destination was designed but would like to see if SAS R&D  can come up with some good improvements in the near future.

 This improvement would also be useful for companies which have  BASE SAS   but not necessarily are willing to invest money to buy SAS/ACCESS  Interface to PC Files software and thus using PROC EXPORT with .xlsx files is completely ruled out.

What do others think of this?

[ballardw]

<Rant mode: ON>

Once upon a time a "page" was often referred to as about 80 lines of text. 100k rows would therefore be roughly equivalent to 1,250 pages. So I strongly suspect that "look at" is really not what users would do. It would take a considerable amount of time to actually "look at" that much data.

I propose that actually creating reports of what the users are looking for would likely be more value added that just providing ever larger files.

</Rant mode: OFF>

[pchegoor]

@ballardw    :   Thanks for the Humor 

Totally agree with what you have said. Such large data is not something which is suited for viewing via an Excel file usually. But sometimes these  Users insist they want it presented that way. 

However, I  do notice that ODS EXCEL often has Memory Issue sometimes even with a Table of let's say 50K rows and 10 columns. I  just wish this Issue with ODS EXCEL is addressed sometime in Near future.

[ballardw]

---

However, I  do notice that ODS EXCEL often has Memory Issue sometimes even with a Table of let's say 50K rows and 10 columns. I  just wish this Issue with ODS EXCEL is addressed sometime in Near future.

---

Do you close down the ODS HTML or other open ODS destinations besides ODS Excel? Formatting for multiple destinations may be using more memory.

[pchegoor]

@ballardw       Yes, I do use the following statements before opening the ODS EXCEL destination.

ODS _ALL_ Close;

ODS RESULTS OFF;

[KentL]

I completely agree with and support @pchegoor 's request.  Recently I was running some tests in the EG environment with ODS EXCEL.  It took approx. 60 secs to export 31,500 rows with 4 columns using ODS EXCEL and approx. 10 minutes to export 65,300 rows * 33 columns!  That is NOT a large amount of data.  SAS should be able to do better.  Please fix!!!