CODEWITHSUNDEEP
sasproc-sql
Timothy
Timothy

Reputation: 35

Proc SQL Running out of Memory

I have a data set that has about 8,000 variables and 100,000 rows. I currently have a Proc SQL statement that takes creates a table that has the counts of the rows and sums from 5,500 different columns that are binary. Example:

Select Count(*) as CNT
,SUM(Column1) as Column1
,SUM(Column2) as Column2
....
,Sum(Column5500) as Column5500
From Table

I'm getting error this error:

ERROR: where clause processing could not obtain memory.

I believe that this is coming from SAS hitting a memory limit. I don't have access to the config file to adjust the memory size.

Here are the list of concerns I have:

  1. I need this statement to run a in a single statement, but not necessarily in Proc SQL. The process that uses this SQL code requires different number columns to be summed each time. Splitting will complicate other parts of the process.
  2. The code is erroring out on 8 GB of memory. The file is only 475 MB and for 5500 variables to be summed, each variable is taking over 10 MB of memory to sum. Is there any way to control the amount of memory being used by each variable?
  3. Is there any documentation on how SAS locates memory while processing?

Upvotes: 0

Views: 1026

Answers (2)

Timothy
Timothy

Reputation: 35

After working through this problem, the issue appears to be naming the response column the same as the source column, E.G. SUM(Column1) as Column1. If code was changed to SUM(Column1) as Column1_s the issue disappears.

Upvotes: 0

Tom
Tom

Reputation: 51566

No idea why it should require a large amount of memory to just sum 1's and 0's.

Try using PROC SUMMARY instead.

proc summary data=have ;
   var column1-column5500;
   output out=want(rename=(_freq_=cnt)) sum= ;
run;

Upvotes: 4

Related Questions