topic Re: ODS Excel PROC Report & PROC PRINT insufficient memory in SAS Programming

ODS Excel PROC Report & PROC PRINT insufficient memory

DerekD_WF — Thu, 29 Apr 2021 13:05:21 GMT

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-because-of/m-p/333552

https://communities.sas.com/t5/SAS-Programming/SAS-System-stopped-processing-this-step-because-of-insufficient/m-p/602984

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-Excel-and-Insufficient-Memory-Error-how-to-get-around/m-p/320661#M17555

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

Re: ODS Excel PROC Report & PROC PRINT insufficient memory

Kurt_Bremser — Thu, 29 Apr 2021 13:12:06 GMT

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

Re: ODS Excel PROC Report & PROC PRINT insufficient memory

SASKiwi — Thu, 29 Apr 2021 20:18:28 GMT

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.

Re: ODS Excel PROC Report & PROC PRINT insufficient memory

ChrisNZ — Fri, 30 Apr 2021 03:17:40 GMT

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

Re: ODS Excel PROC Report & PROC PRINT insufficient memory

DerekD_WF — Fri, 30 Apr 2021 13:08:25 GMT

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.

Re: ODS Excel PROC Report & PROC PRINT insufficient memory

Kurt_Bremser — Fri, 30 Apr 2021 14:03:33 GMT

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.

Re: ODS Excel PROC Report & PROC PRINT insufficient memory

DerekD_WF — Fri, 30 Apr 2021 14:10:14 GMT

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?

Re: ODS Excel PROC Report & PROC PRINT insufficient memory

Kurt_Bremser — Fri, 30 Apr 2021 14:14:44 GMT

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;

Re: ODS Excel PROC Report & PROC PRINT insufficient memory

DerekD_WF — Fri, 30 Apr 2021 15:29:12 GMT

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