Hi Team,
I have fetch the 1384026 records from the table and trying to export into .xlsx file.
Getting error:
Error: cli execute Microsoft odbc excel driver spreadsheet is full.
Code:
proc datasets lib=_ibxlsx;
delete data;
run;
proc append data=_ibxlsx.data base= abc;
run;
PROC EXPORT DATA= _ibxlsx.data
OUTFILE= 'C:\excel files\data.xls'
DBMS=EXCEL REPLACE;
SHEET="data";
RUN;
I know the threshold of xlsx is 1048576.is it possible to increase the threshold value from sas to export.
can you please guide me??
@rinsabraham wrote:
Hi Team,
I have fetch the 1384026 records from the table and trying to export into .xlsx file.
Getting error:
Error: cli execute Microsoft odbc excel driver spreadsheet is full.
Code:
proc datasets lib=_ibxlsx;
delete data;
run;
proc append data=_ibxlsx.data base= abc;
run;
PROC EXPORT DATA= _ibxlsx.data
OUTFILE= 'C:\excel files\data.xls'
DBMS=EXCEL REPLACE;
SHEET="data";
RUN;
I know the threshold of xlsx is 1048576.is it possible to increase the threshold value from sas to export.
can you please guide me??
Right now you are creating an xls-file, limited to 65k rows, change dbms and file-extensions to xlsx.
Thanks for the quick reply
let me try and get back to you.
but one more doubt my colleguage tried to copy that file to some other location getting the same error.
code:
proc datasets lib=_ibxlsx;
delete data;
run;
proc append data=_ibxlsx.data base= abc;
run;
data _null_;
src== 'C:\excel files\data.xls';
dest== 'C:\excel files\out\data.xls';
if (fileexist (src)) then do
call system ('copy' ||quote(src) ||' ' ||quote(des));
end;
getting the same error
I have changed the dbms to xlsx and changed the file extension still getting the same error.
Please post your code and log, all we can see above is that you are exporting to XLS which is an ancient binary file format. What operating system are you running on, how are you exporting the data etc.
Sorry I can't paste the log because it is in client server.
operating system: windows 7
But now I have removed the duplicate records so I got less than 1 million records so I can exported the data now. previously I didn't remove the duplicate records due to which data has came up more than 1 million.
Thanks you for reply..
"Sorry I can't paste the log because it is in client server." - then how do you know what is going on? Log is a fundamental things to see what is going on with the process.
"But now I have removed the duplicate records so I got less than 1 million records" - why on earth are you pushing out millions of records to a spreadsheet? That is truly madness. No-one will ever look at a million records in Excel, nor will that format be useful for anyone. If you are transferring data then use CSV, Json, XML, trusted platform independent streamable data sources. Excel is really not the tool for this (whatever your process is).
ok
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: