BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rdic65
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
rdic65
Fluorite | Level 6

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;

 

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Fluorite | Level 6

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1788 views
  • 4 likes
  • 2 in conversation