Memory shortage error

[SMarieH1521]

I am creating a workbook. I wrapped the code in a macro so that I don't have to repeat the code for each group. The macro is causing a memory error. Is there a way to bypass this issue? The code works w/o issue when its not wrapped in the macro. The table the code is reading from has 36K observations. SAS BASE 9.4. 

Code: 

options FULLSTIMER;

%MACRO RECONBOOKS (AFF=, MTH=);
 ODS EXCEL FILE = "C:\Filelocation\&AFF..xlsx" style=dove;

ODS EXCEL OPTIONS (SHEET_NAME= 'EPISODES TO RECONCILE' EMBEDDED_TITLES='YES' AUTOFILTER='ALL');
TITLE HEIGHT=16PT 'RECONCILE';
Proc report DATA = SITE missing
STYLE(HEADER) = {FONT_SIZE=12PT FONTWEIGHT=BOLD COLOR=BLACK}
STYLE(COLUMN) = {FONT_SIZE=10PT}
STYLE(SUMMARY) = {FONT_SIZE=10PT FONTWEIGHT=BOLD BACKGROUND=GAINSBORO FONT_STYLE=ROMAN};
Where AFFILIATE = &AFF. and MONTH= &MTH.;
COLUMN (CASEID CONTRACTNAME MEMBERNAME EXTERNALID BIRTHDATE LOB FULL_NM SITETYPE SITE EPISODESTART EPISODEEND RENDERING BILLING INDEXWINDOW CLAIM SERVICEDATE SERVICES PRIMARYDX DAYSPOSTINDEX TYPE CHARGE MEMBERLIABILITY PAID);
Define CASEID/ ORDER ;
DEFINE CONTRACTNAME/ 'CONTRACT NAME' order;
DEFINE MEMBERNAME/ 'MEMBER NAME' order;
DEFINE SITE/ DISPLAY;

Define RENDERING /DISPLAY;
DEFINE BIRTHDATE/'BIRTH DATE' DISPLAY LEFT;
DEFINE FULL_NM/'SURGEON' DISPLAY;
DEFINE EPISODESTART/'EPISODE START' DISPLAY LEFT;
DEFINE EPISODEEND/'EPISODE END' DISPLAY LEFT;
DEFINE SERVICEDATE /'SERVICE DATE' DISPLAY LEFT ;
DEFINE DAYSPOSTINDEX/'DAYS POST INDEX' DISPLAY LEFT;
DEFINE CLAIM/ 'CLAIM' DISPLAY ;
DEFINE CHARGE/ 'CHARGE' ANALYSIS FORMAT=DOLLAR14.2;
DEFINE MEMBERLIABILITY/ 'MEMBER LIABILITY' ANALYSIS FORMAT=DOLLAR14.2;
DEFINE PAID/ 'PAID' ANALYSIS FORMAT=DOLLAR14.2;
COMPUTE CASEID;
IF _BREAK_ NE '_BREAK_' THEN DO;
CASEID NE ' ' THEN HOLD=CASEID;
CASEID EQ ' ' THEN CASEID= HOLD;
END;
ENDCOMP;
COMPUTE CONTRACTNAME;
IF _BREAK_ NE '_BREAK_' THEN DO;
IF CONTRACTNAME NE ' ' THEN HOLD1=CONTRACTNAME;
IF CONTRACTNAME EQ ' ' THEN CONTRACTNAME= HOLD1;
END;
ENDCOMP;
COMPUTE MEMBERNAME;
IF _BREAK_ NE '_BREAK_' THEN DO;
IF MEMBERNAME NE ' ' THEN HOLD2=MEMBERNAME;
IF MEMBERNAME EQ ' ' THEN MEMBERNAME= HOLD2;
END;
ENDCOMP;
BREAK AFTER CASEID/SUMMARIZE;
compute after CASEID;
RENDERING = 'GRAND TOTAL';
ENDCOMP;
RUN;

 ODS EXCEL CLOSE;
%MEND RECONBOOKS;

 %RECONBOOKS(AFF='Name1', MTH='October')

***************************************************************************************

Log: 

NOTE: There were 1870 observations read from the data set WORK.SITE.
WHERE (AFFILIATE='Name1') and (MONTH='October');
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.84 seconds
user cpu time 0.01 seconds
system cpu time 0.06 seconds
memory 2606.92k
OS Memory 2088740.00k
Timestamp 06/27/2024 12:06:15 PM
Step Count 91 Switch Count 0

ERROR: A critical memory shortage occurred while extending a crossing table.
NOTE: The initial memory limit for classification tables was 6975K bytes. Actual memory acquired was
6144K bytes.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1548 observations read from the data set WORK.SITE.
WHERE (AFFILIATE='Name1') and (MONTH='October');
NOTE: PROCEDURE REPORT used (Total process time):
real time 0.72 seconds
user cpu time 0.00 seconds
system cpu time 0.06 seconds
memory 10193.84k
OS Memory 2097148.00k
Timestamp 06/27/2024 12:06:15 PM
Step Count 92 Switch Count 0

[ballardw]

I won't swear that this will fix the problem but you are passing quotes as part of the macro variables. So when &aff is used in

ODS EXCEL FILE = "C:\Filelocation\&AFF..xlsx" style=dove;

it would resolve to

ODS EXCEL FILE = "C:\Filelocation\'Name1'.xlsx" style=dove;

and it is seldom a good idea to include quotes in file names.

I would suggest changing the call to avoid the quotes by changing your

Where AFFILIATE = &AFF. and MONTH= &MTH.;

to be

Where AFFILIATE = "&AFF." and MONTH= "&MTH.";

Then call the macro with

%RECONBOOKS(AFF=Name1, MTH=October)

[SMarieH1521]

Thank you! I was passing the quotes to my excel export and this did stop that from occurring. Unfortunately, I am still working to resolve the space issue. 

[Ksharp]

It looks like you have a big table to generate a big report to cost too much memory.
The most validate way is add option -memsize 60G in your sasv9.cfg configure file to have you more memory.
Another way you could try is closing all the destination before invoking your macro to save your memory, like :



ods noresults;
ods _all_ close;
%RECONBOOKS(AFF='Name1', MTH='October')

[Sajid01]

Hello @SMarieH1521 
I see this in the log output you have posted : OS Memory 2097148.00k.
The default value of -memsize option is 2 GB. This could be one reason for the error.
You can progressively increase this value  till you get the result.
If your job is the only one running process, then a value of MAX can be used.
Bear in mind that in addition to SAS, OS and other processes will be consuming memory.
Once the available RAM is exceeded, depending on the OS settings, memory contents will be swapped to disk and this slows down the system   and eventually   the system may hang.