Format Excel sheet, SAS on server, too much data for normal methods

[DavidDW]

I've created a multipage Excel report. The best tool I've found for most of the pages is ODS Excel with PROC REPORT, which will do far more than I'll ever need except in the case of one sheet that has several hundred columns and tens of thousands of rows. I'm using EG 7.13 HF6 (7.100.3.5513 64 bit) connecting to SAS on a server and getting out of (virtual) memory errors after a few minutes of processing. There is now a lot of disk space on that server. If there's a configuration suggestion, I'll pass it on to our SAS admin, who is not much of a SAS user otherwise.

So, I can't use ODS EXCEL with PROC REPORT or PROC PRINT or whatever.

I can't use DDE to send commands to an open Excel session reliably with SAS on a server, AFAIK. I've done it a lot on PC SAS, and even then there are glitches, like when another application pops up and grabs the Windows focus during a DDE operation.

PROC EXPORT and LIBNAME ... XLSX methods don't allow me to format the cells/columns (CURRENCY, BORDERS, Filters, etc).

I see someone mentioned using a JAVA bridge, though I haven't looked into it, partly because I hesitate to drag another tool and environment into it all.

I've used JScript Windows Script Host scripts before to operate on Excel sheets and I considered that, maybe kicked off by EG. But in our environment WSH scripts are blocked.

I'd rather not write VBA and have to have someone run them separately from the EG job that creates the output (aside from hating every dialect of BASIC I've ever seen, and I've used them a lot).

What else can I do? I've run out of ideas.

Thanks for any suggestions.

David

Greensboro, NC

[Reeza]

Can you do a two step process, first exports everything else using ODS EXCEL and then do a PROC EXPORT into the same file to get the table data into the file? Haven't tested this but don't see why it wouldn't work.

[DavidDW]

Yes, this works, it's what I'm doing. I just can't control the format on the sheet that's output with PROC EXPORT or LIBNAME ... XLSX.

Thanks

[Reeza]

Here's the usage note for you, if you're going to follow up with Tech Support. 

I've assumed you've already modified your MEMSIZE as well, otherwise you can try that. 

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

[ballardw]

Sometimes memory issues can be somewhat resolved by closing the HTML destination. If that and the ODS Excel are both open then you are generating twice (roughly) as much output. To a much smaller extent choice of ODS style may also reduce memory use, Journal for instance.

However I would first question why you are providing "several hundred columns and tens of thousands of rows."

Who is actually going to read all that? The only reason to have formatted cells is for a person to read them.

[Reeza]

One usage, is when you provide reports that also include the source data for the report requester to enable further drill downs or analysis on their own. Or as part of a required deliverable. So you have a few sheets with summaries and then they get 'deeper' output. I can see some cases where this does occur.

I would also report this to SAS Tech Support, though I believe it's a known issue at the moment. Hopefully they're working on a solution.

[DavidDW]

Report design has been around for years and is fixed, I'm just trying to replicate it.

I'll do that. Perhaps there's a server setting that would cure the issue...?

[Reeza]

---

@DavidDW wrote:

Report design has been around for years and is fixed, I'm just trying to replicate it.

I'll do that. Perhaps there's a server setting that would cure the issue...?

---

The MEMSIZE setting. Check that first.

[DavidDW]

I'm doing ODS LISTING CLOSE before issuing ODS EXCEL...    Is there something else that needs to be shut off?

If it was up to me, there wouldn't be this much stuff. It's replicating a report that was produced elsewhere which people expect to be a certain way and things have been built that depend on that.

[Kurt_Bremser]

---

@DavidDW wrote:

I'm doing ODS LISTING CLOSE before issuing ODS EXCEL...    Is there something else that needs to be shut off?

---

Turn off all result destinations in Enterprise Guide (Tools - Options - Results). Then you don't need to close anything in the code (EG issues a ods _all_ close on its own before opening all destinations specified by the EG settings).

ods listing close will only be needed for programs that will run in batch.

[Kurt_Bremser]

Turn off all results in Enterprise Guide, so that the only open ODS destination is the Excel file on the server.