topic Re: How to export a large dataset into multiple sheets in same excel in SAS Programming

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

Balaji_7992 — Sun, 10 May 2020 16:23:13 GMT

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

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

PaigeMiller — Sun, 10 May 2020 16:35:57 GMT

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.

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

Ksharp — Mon, 11 May 2020 11:24:56 GMT

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;
...............