topic SQL Memory Usage in SAS Programming

SQL Memory Usage

ods_trace_on — Wed, 26 Feb 2020 21:38:46 GMT

If I run a code like one below, SAS (9.04.01M5P091317) seems to be taking up more and more memory with every loop without ever clearing it and eventually runs out of it. It only happens with the median function. The total memory usage is almost constant when I use 'mean' or 'distinct' instead. What's going on?

%macro loop(n);
	%do i = 1 %to &n.;
		proc sql; 
			create table out as select 
			median(AgeCHDdiag)
			from sashelp.heart;
		quit;
	%end;
%mend;
%loop(1000);

Re: SQL Memory Usage

ChrisNZ — Thu, 27 Feb 2020 02:40:24 GMT

This looks like a bug to me.

You can check the available memory with option XMRLMEM.

Report the issue to SAS Tech support.

Re: SQL Memory Usage

Tom — Thu, 27 Feb 2020 03:13:24 GMT

Report it to SAS. http://support.sas.com

Use PROC MEANS (aka PROC SUMMARY) or PROC UNIVARIATE instead of calculate medians.

Re: SQL Memory Usage

Patrick — Thu, 27 Feb 2020 03:18:27 GMT

@ods_trace_on

I'm on the same SAS version 9.04.01M5P091317 under RHEL and can confirm your observation.

Please raise this with SAS Tech Support.

Re: SQL Memory Usage

andreas_lds — Thu, 27 Feb 2020 06:40:18 GMT

Problem confirmed using the same version on windows server 2012r2.

Memory stays used after proc sql ends.