fix bug in ods excel: ODS Excel and Insufficient Memory Error

[tomrvincent]

for even moderate sized tables (80,000 recs) ODS EXCEL can blow out of memory while proc export works just fine.

There are some useful features to ODS EXCEL but it can only be used on small tables.  I have MEMSIZE=2147483648 and yet SAS crashes with 80k recs.

[ballardw]

ODS destinations include formatting information. You don't describe exactly what "blow of memory" may entail but large tables with lots of formatting add lots of information to the data needed. A choice of a minimalist style such as Journal may reduce memory use.

Also it will likely help to ensure other ODS destinations are closed as HTML also can use a lot of memory at the same time.

If this is related to your previous question about setting column widths because you have a client that is too, let us say, "unsophisticated" to set column widths in Excel to read the data then it might be worth discussing the various costs to the client are incurred by having to do such things for the client they could well do themselves.

If someone is actually reading all 80,000 rows of data there is something sort of off kilter in the whole project IMHO. That would be similar to reading a 2,000 page paperback novel.

[Reeza]

What version of SAS are you using? This was a known issue before it was in PROD but I thought it was resolved as of SAS 9.4TS1M3 which is the first production version of ODS EXCEL.

[tomrvincent]

not "blow of memory".  blow OUT of memory.  I don't know what the client intends to do with the data...above my pay grade.

Anyway, I came up with a hack to get around it: forget about ODS, go back to proc export and use this VB macro for each and every file.  I was trying to avoid that because ODS solves the problem...but not for more than about 10,000 recs or so.

Sub x()
Range("f2", "f999999").NumberFormat = "0000000000000"
Range("J2", "j999999").NumberFormat = "yyyy-mm-dd"
Range("k2", "k999999").NumberFormat = "yyyy-mm-dd"
Range("l2", "l999999").NumberFormat = "yyyy-mm-dd"
Range("m2", "m999999").NumberFormat = "yyyy-mm-dd"
Range("n2", "n999999").NumberFormat = "yyyy-mm-dd"

End Sub

[tomrvincent]

@Reeza 9.04

[Reeza]

9.4 has at least 5 versions so far, you need to show the TS1MXXX portion. 

You can do that using 

proc product_status;run;

[tomrvincent]

@Reeza 9.4_M3

[Reeza]

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

[tomrvincent]

I've seen that before. Memory is as listed above...that shouldn't blow up with 80k records.

[Vince_SAS]

You can try starting SAS using the MEMSIZE=max option.

Vince DelGobbo

SAS R&D

[tomrvincent]

@Vince_SAS so just something like 

"C:\Program Files\SASHome_94\SASEnterpriseGuide\7.1\SEGuide.exe" -MEMSIZE=max ?