DATA Step, Macro, Functions and more

proc export xlsx abnormal memory consumption

Reply
Frequent Learner
Posts: 1

proc export xlsx abnormal memory consumption

Hi all,
I'm experimenting a strange behaviour with proc export with XLSX engine: I need to do a loop that creates many many Excel files, for example 100000 files, of little dimensions, every file is 50 KB.
when I run the program I observe that my sas session increases the occupation of memory without ever releasing it until it reaches 2GB and then crashes. 2GB is the set of memsize option in my config.

 

Here below an example code:

 

%macro test;
%let n=100000;
%do i=1 %to &n;

data _NULL_;
set Riferimenti;
WHERE Num_report=&i.;
call symput('N_PRATWCT',strip(put(COD_PRAT_MBCS,8.)));
call symput('N_PRATORIG',strip(put(PARD_PRATICA,8.)));
call symput('NameFile',"60000_"||strip(put(COD_PRAT_MBCS,8.)));
run;


data Dati;
set Guida;
where COD_PRAT_MBCS=&N_PRATWCT.;
run;


proc export data=Dati
OUTFILE="D:\test\files\&NameFile..xlsx"
DBMS=xlsx
REPLACE LABEL;
SHEET="&N_PRATORIG";
run;

%end;
%mend;

%test;


I'm running that in SAS Foundation 9.4.

 

To overcome this problem I made several attempts and I found that the problem is actually the XLSX engine. I replaced xlsx with xls and everything is back to normal. Even with csv everything is ok. And even using ODS everything ok, only with ODS the creation of files becomes very slow.
I also tested the adoption of libname xlsx with a similar result of proc export.

 

Did it happen to someone else?
Thanks

Roberto

Super User
Posts: 9,548

Re: proc export xlsx abnormal memory consumption

If you are on the latest maintenance level (M5), opening a track with SAS technical support is in order, as this smells like a real bug.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,548

Re: proc export xlsx abnormal memory consumption

PS Kudos for thorough testing:

 

To overcome this problem I made several attempts and I found that the problem is actually the XLSX engine. I replaced xlsx with xls and everything is back to normal. Even with csv everything is ok. And even using ODS everything ok, only with ODS the creation of files becomes very slow.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 2 replies
  • 69 views
  • 4 likes
  • 2 in conversation