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