How to export a large dataset into multiple sheets in same excel

[Balaji_7992]

I do have one dataset having 10,000 observations. Here i need to export 500 observations each sheet in same excel file.

[PaigeMiller]

Create a variable (which I shall name GROUP) such that the first 500 records have value 1, and the next 500 have value 2, and so on; and then do PROC PRINT with a BY statement and use the ODS EXCEL option sheet_interval='BYGROUP'

Example:

ods excel file='myexcelfilename.xlsx' options(sheet_interval="BYGROUP");
proc print data=have;
    by group;
run;
ods excel close

or, thinking out of the box, make your life easy, don't split the data up like this, which is much easier to do, and probably much easier on whoever is going to receive this Excel file as well.

[Ksharp]

Also could run multiple RPOC EXPORT (write a macro to wrap it)

proc export data=have(obs=500) outfile='c:\temp\want.xlsx' dbms=xlsx replace;
sheet='1-500';
run;

proc export data=have(firstobs=501 obs=1000) outfile='c:\temp\want.xlsx' dbms=xlsx replace;
sheet='501-1000';
run;
...............