proc export xlsx abnormal memory consumption

[rdic65]

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

[Kurt_Bremser]

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.

[Kurt_Bremser]

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.

[rdic65 · Accepted solution]

It is official, it's a defect!

Maybe there will be a fix or it will be included in a later version.

Here is the example code:

%let tot=10000;

data pippo;
format a 8. b $32.;
do i=1 to &tot.;
a = i;
b = "ciao" || put(i, 8.);
output;
end;
drop i;
run;

%macro test;
%do i=1 %to &tot.;
data pluto;
set pippo;
WHERE a=&i.;
run;

proc export data=pluto
OUTFILE="c:\appo\test\test_&i..xlsx"
DBMS=xlsx
REPLACE LABEL;
run;
%end;

%mend;

%test;