ODS Excel PROC Report & PROC PRINT insufficient memory

[DerekD_WF]

I am attempting to create a 'report' (more like a listing of rows from a dataset) utilizing ODS Excel and keep running into the "ERROR: The SAS System stopped processing this step because of insufficient memory." problem. I read through a handful of other posts on this topic:

https://communities.sas.com/t5/SAS-Programming/Insufficiency-memory-error/m-p/444989 

https://communities.sas.com/t5/SAS-Programming/ERROR-The-SAS-System-stopped-processing-this-step-bec... 

https://communities.sas.com/t5/SAS-Programming/SAS-System-stopped-processing-this-step-because-of-in... 

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Excel-and-Insufficient-Memory-Error-how-to... 

https://communities.sas.com/t5/SAS-Programming/error-with-proc-report/m-p/706206#M216729 

Below is from the log with FULLSTIMER option set:

118 ods select all;
119 ods excel options(sheet_interval="none" sheet_name="list");
120
121 proc report data=work.CA_rpt1;
122
< 19 columns >
126
< I define / display the same 19 variables here>
146 run;

NOTE: Multiple concurrent threads will be used to summarize data.
ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 56673 observations read from the data set WORK.CA_RPT1.
NOTE: PROCEDURE REPORT used (Total process time):
real time 17.50 seconds
user cpu time 15.47 seconds
system cpu time 2.00 seconds
memory 3658694.14k
OS Memory 4194180.00k
Timestamp 04/29/2021 05:13:57 AM
Step Count 11 Switch Count 18
Page Faults 0
Page Reclaims 1382625
Page Swaps 0
Voluntary Context Switches 160
5 The SAS System

Involuntary Context Switches 9
Block Input Operations 16
Block Output Operations 0

My information: 

I am running in SAS Enterprise Guide 7.15 (7.100.5.5850) (64-bit), connected to a Linux server. 

88
89 proc product_status; run;

For Base SAS Software ...
Custom version information: 9.4_M6

85
86 proc options option=memsize;
87 run;

MEMSIZE=4294967296
Specifies the limit on the amount of virtual memory that can be
used during a SAS session.

Whereas for PROC Export:

NOTE: The export data set has 56673 observations and 20 variables.
NOTE: "" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 3.77 seconds
user cpu time 3.65 seconds
system cpu time 0.10 seconds
memory 30174.40k
OS Memory 80616.00k
Timestamp 04/29/2021 06:01:43 AM
Step Count 15 Switch Count 4
Page Faults 0
Page Reclaims 23209
Page Swaps 0
Voluntary Context Switches 18
Involuntary Context Switches 10
Block Input Operations 2089
Block Output Operations 9872

I initially tried using a simple proc print instead of proc report, but that runs into the same insufficient memory error so I assume this to be an ODS Excel issue and not anything with PROC Print or PROC Report. In addition, I can just perform a PROC Export which creates an Excel file ~4.9MB in size. Does it really consume 100x (Export=30,174.40k vs ODS Excel Report=3,658,694.14k) the memory to create an Excel file with ODS Excel vs PROC Export for XLSX? While export is a viable alternative, as part of the script I also create a couple of summaries using PROC Tabulate on separate Excel sheets and was hoping to contain all output in a single Excel file for my end users.

Is the only work around to get MEMSIZE increased? I have reached out to our internal SAS/server team for assistance as well as I am not sure I have any control over 

[Kurt_Bremser]

The EXPORT is much more efficient because it does not set any (or most) cell attributes, which eat a LOT of space on the XML (before compressing).

[SASKiwi]

In my experience ODS is VERY resource intensive. It's fine for creating printed reports, but if you start writing tens of thousands of rows of output it consumes large amounts of CPU and memory. I'm not at all surprised by your findings. PROC EXPORT is a better option in your case. 

[ChrisNZ]

> I am attempting to create a 'report' (more like a listing of rows from a dataset) utilizing ODS Excel

Any reason to use ODS?

If you really need to, and MEMSIZE is locked, then you might be able to fill in the excel sheet in several passes.

[DerekD_WF]

the reason for ODS Excel is because I am also including a couple of summary outputs from PROC Tabulate on a worksheet separate from the listing of the detailed records. 

[Kurt_Bremser]

First run the Reports with ODS EXCEL; reports should be fairly small. After the ODS EXCEL CLOSE, open the Excel file with LIBNAME XLSX and use PROC COPY (or DATA steps) to copy the datasets to the workbook.

[DerekD_WF]

Thanks, I will give that a try. After creating the reports with ODS Excel and ODS Excel close, can I do a proc export to that file in a new sheet name without overwriting the file?

[Kurt_Bremser · Accepted solution]

This should also be possible; try it.

This worked with University Edition:

ods excel
  file="/folders/myfolders/test.xlsx"
  options(sheet_interval="none" sheet_name="class")
;

proc print data=sashelp.class;
run;
ods excel close;

proc export
  data=sashelp.cars
  file="/folders/myfolders/test.xlsx"
  dbms=xlsx
  replace
;
sheet="cars";
run;

[DerekD_WF]

Updated my code and tested and this solution worked for me as well. Thanks for the suggestion!