I quite agree with @Kurt_Bremser, you could not pick a worse format for transporting data. You will encounter all kinds of issues.
Generally speaking you have several options to export data to Excel:
ods tagsets.excelxp - this will generate XML which Excel can read in easily, its not real Excel though
proc export - generates a binary Excel file
libname excel - not really used much, but access xlsx directly
Now you could code as (using proc export);
data _null_; set sashelp.vtables (where=(libname="YOURLIB" and memname="YOURDS")); do i=1 to nobs / 10000; call execute('data temp; set yourlib.yourds; if '||strip(put((i-1)*10000,best.))||' <= _n_ < '||strip(put(i*10000,best.))||' then output; run;'); call execute('proc export data=temp outtfile="c:\myoutputfile.xls"; sheet="Sheet'||strip(put(i,best.))||'"; run;'); end; run;
What this does is loop from 1 to nobs/10000 times and for each loop generates a datastep which filters records for that group, then proc export code to export that dataset to a sheet.
However, one last time, do everything possible to avoid using Excel, it is not a useable data format in any sense, and most people will refuse it.
Simple tip:
DO NOT USE THE EXCEL FILE FORMAT FOR DATA TRANSFER!!
A csv file does not have the limitations of the Excel kludge, is readable with a text editor for control, and works reliably with lots of applications.
And if you have data that exceeds the limits for a spreadsheet (which are way too high anyway, as nobody can keep control of a spreadsheet with > 1000 lines), why are you trying to "use" it in Excel? You have SAS at your hands, which is more powerful than Excel by orders of magnitude.
If you want to use Excel for nice displays, that's OK, but do the heavy work in SAS and only hand the compacted results over to Excel.
Tell your client that the data is too large for Excel, and deliver the data in a csv. When your client tries to open that in Excel, they will hopefully realize their foolishness.
Everything else is unethical, IMO.
Depends on two things really - firstly the experience of the person getting the Excel file of course. If they haven't hit some of the walls provided by the format, then they will know no different. However there is a secondary reason as well. Excel's popularity stems from the very simple and easy to use Front End, so if your a Front End user then you will love it - its why management use it, its simple and can produce colorful nonsense for them to mull over for hours on end before dropping whole departments. These users do not see the back end of it and hence have no issues. It avoids all that nastiness of setting up entry screens, creating edit checks and audit trails and such like, in favour of moving all that responsiblity from them and their database, to the end user of the data.
This is why now, rather than try to explain it, any time anyone sends me an Excel file, or states they will use it, I simply increase the budgetary need accordingly, I am sure most groups are able to understand financial implications.
And of course, slight disclaimer, there are some who have no option than to use it, however with a direct CSV exporter in Excel, or some knowledge of VBA this shouldn't be an excuse.
I quite agree with @Kurt_Bremser, you could not pick a worse format for transporting data. You will encounter all kinds of issues.
Generally speaking you have several options to export data to Excel:
ods tagsets.excelxp - this will generate XML which Excel can read in easily, its not real Excel though
proc export - generates a binary Excel file
libname excel - not really used much, but access xlsx directly
Now you could code as (using proc export);
data _null_; set sashelp.vtables (where=(libname="YOURLIB" and memname="YOURDS")); do i=1 to nobs / 10000; call execute('data temp; set yourlib.yourds; if '||strip(put((i-1)*10000,best.))||' <= _n_ < '||strip(put(i*10000,best.))||' then output; run;'); call execute('proc export data=temp outtfile="c:\myoutputfile.xls"; sheet="Sheet'||strip(put(i,best.))||'"; run;'); end; run;
What this does is loop from 1 to nobs/10000 times and for each loop generates a datastep which filters records for that group, then proc export code to export that dataset to a sheet.
However, one last time, do everything possible to avoid using Excel, it is not a useable data format in any sense, and most people will refuse it.
You have a dumb requirement, and dumb requirements tend to end up as dumb (unnecessarily complicated) code.
How do you mean simplify? You could use cat() function if you don't like ||, but not sure how simpler you could make it as this only contains the necessary parts:
data _null_; set sashelp.vtables (where=(libname="YOURLIB" and memname="YOURDS")); do i=1 to nobs / 10000;
low=strip(put((i-1)*10000,best.));
high=strip(put(i*10000,best.));
sheet=cats("Sheet",put(i,best.)); call execute(cat('data temp; set yourlib.yourds; if ',low,' <= _n_ < ',high,' then output; run;'); call execute(cat('proc export data=temp outtfile="c:\myoutputfile.xls"; sheet="',sheet,'"; run;'); end; run;
Maybe that looks simpler?
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 25. 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.