BookmarkSubscribeRSS Feed
Balaji_7992
Calcite | Level 5

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

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Ksharp
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2233 views
  • 3 likes
  • 3 in conversation