topic ods excel out of memory in SAS Programming

ods excel out of memory

acordes — Thu, 19 Jan 2023 12:20:05 GMT

Inside the ods block I have several proc reports (not shown), I want to populate one worksheet with a table using proc print.

For 10000 rows it works, but for the whole table (200k rows) it gives the following error:

ERROR: The SAS System stopped processing this step because of insufficient memory.

options casdatalimit=all;

ods _all_ close;
ods excel file="/caslibs/marketing/testing.xlsx" 
options(sheet_interval='none' sheet_name='All VWFS');

proc print data=MKT.FATFISH_LCV2;
run;

ods excel close;

Re: ods excel out of memory

acordes — Thu, 19 Jan 2023 12:40:56 GMT

This solution from @Kurt_Bremser works for me:

options casdatalimit=all;

ods _all_ close;
ods excel file="/caslibs/marketing/testing2.xlsx" 
options(sheet_interval='none' sheet_name='All VWFS');

proc print data=MKT.FATFISH_LCV2(obs=1000);
run;

ods excel close;

proc export
  data=MKT.FATFISH_LCV2(obs=10000)
  file="/caslibs/marketing/testing2.xlsx"
  dbms=xlsx
  replace
;
sheet="cars";
run;

Re: ods excel out of memory

Kurt_Bremser — Thu, 19 Jan 2023 15:17:56 GMT

ODS is good for readable reports, not for mass data transfer. Use PROC EXPORT or LIBNAME XLSX.