Insufficient memory error for processing large datasets

deleted_user — Tue, 13 Jul 2010 13:36:33 GMT

Hi all,

Summary of problems occurred in my job:

If we take the log just before the first (Insufficient memory) error :
NOTE: Renaming a table ...
MPRINT(CALL_INBOUND_JOB): proc datasets lib = work nolist nowarn memtype = (
MPRINT(CALL_INBOUND_JOB): change etls_M601CR_FR = M601CR_FR;
MPRINT(CALL_INBOUND_JOB): quit;

NOTE: Changing the name WORK.ETLS_M601CR_FR to WORK.M601CR_FR (memtype=DATA).
NOTE: The PROCEDURE DATASETS used the following resources:
BOTTOM OF PAGE 000000032 -----------------------------------------------------
30 The SAS System

CPU time - 00:00:00.00
Elapsed time - 00:00:00.02
EXCP count - 10
Task memory - 471K (40K data, 431K program)
Total memory - 10001K (4896K data, 5105K program)
NOTE: The address space has used a maximum of 3256K below the line and 15052K above the limit

And then comes

MPRINT(CHK_EXCESS_TXTLINE): proc sql noprint;
MPRINT(CHK_EXCESS_TXTLINE): SELECT DISTINCT TRANS_ID INTO :mv_List_Trans_Id
((EXCESS_052_FLG = 1 OR EXCESS_062_FLG = 1 ) AND REC_TYP IN ('052', '062')) ;
NOTE: No rows were selected.
MPRINT(CHK_EXCESS_TXTLINE): quit;
NOTE: The PROCEDURE SQL used the following resources:
CPU time - 00:00:44.50
Elapsed time - 00:01:15.43
EXCP count - 111
Task memory - 1085K (0K data, 1085K program)
Total memory - 784771K (779060K data, 5711K program)
NOTE: The address space has used a maximum of 4908K below the line and 786628K

And then the insufficient memory error comes.

MPRINT(CALL_INBOUND_JOB): ;
MPRINT(CHK_CUST_NO): proc sql noprint;
MPRINT(CHK_CUST_NO): SELECT TRANS_ID into :mv_List_Trans_Id_1 SEPARATED by ',' FROM ( SELECT DISTINCT TRANS_ID, CRED_CUST_NO, 1
AS CNT FROM Work.M601CR_FR WHERE REC_TYP IN ('022', '042', '052', '062') ) GROUP BY TRANS_ID HAVING SUM(CNT) > 1 ;
NOTE: SAS threaded sort was used.
ERROR: Insufficient memory.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of state
MPRINT(CHK_CUST_NO): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: The PROCEDURE SQL used the following resources:
CPU time - 00:01:09.06
Elapsed time - 00:02:06.13
EXCP count - 14958
Task memory - 1253K (0K data, 1253K program)
Total memory - 785579K (779700K data, 5879K program)
NOTE: The address space has used a maximum of 4912K below the line and 791356K

Before and inclusive the step of "renaming table" (the first entry I have listed), - the storage usage above the limit has been no more than 15MB (in fact for many procedures/steps it has been steady on 15052K).
Then comes the "MPRINT(CHK_EXCESS_TXTLINE): proc sql noprint" with the select statement which in fact returns ZERO rows,
but as you can see returning ZERO rows results in an storage increase from the (steady) 15052K to around 780MB

Still not an error, - but as you can see the next step kind of builds on top of the storage allocated, - and after the "Insufficient memory" message the storage allocation is now around 790MB.

So the question is, - should there be somehow a built-in command to release storage after the procedure/step resulting in ZERO rows ?. Does the application miss to do this because ZERO rows returned triggers another code logic path ?.

Re: Insufficient memory error for processing large datasets

sbb — Tue, 13 Jul 2010 14:05:12 GMT

First of all, your SAS log is truncated. Second, the two PROC SQL invocations have different WHERE specifications. Suggest you test your code with a more limiting WHERE clause in the second PROC SQL execution. Also check your SAS OPTIONS settings for GROUP=MEMORY, with PROC OPTIONS.

Scott Barry
SBBWorks, Inc.

Re: Insufficient memory error for processing large datasets

deleted_user — Tue, 13 Jul 2010 14:21:42 GMT

Hi Scott,

The where conditions cant be modified as per functional requirement.
I wanted to know is there any SAS options that can release the program memory of the step after it is executed successfully.
Also the options used are:
MEMLEAVE=256M
PROCLEAVE=(2M,2M)
SYSLEAVE=(512K,512K)
compress=yes reuse=yes STATS MEMRPT FULLSTIMER MSGLEVEL=I bufsize=512K
bufno=MAX SORTSIZE=MAX UBUFNO = 20

Rest are default system options. The SAS server is 9.1 on z/OS

Message was edited by: Pratik Message was edited by: Pratik

Re: Insufficient memory error for processing large datasets

sbb — Tue, 13 Jul 2010 16:18:18 GMT

I might question your BUFNO=MAX setting. Also, you don't mention MEMSIZE= in your list.

You will want to contact SAS Tech Support for further assistance with your situation.

Scott Barry
SBBWorks, Inc.

topic Re: Insufficient memory error for processing large datasets in SAS Procedures

Insufficient memory error for processing large datasets

Re: Insufficient memory error for processing large datasets

Re: Insufficient memory error for processing large datasets

Re: Insufficient memory error for processing large datasets