Export one data set into one XLSX file with multiple sheets-Automat BY

[Ronein]

Hello

I want to export SAS data set into one XLSX file with multiple sheets.

I need to make some changes:

1-The sheets names now are: ByGroup 1 - Asia ,ByGroup 2 - Europe ,ByGroup 3 - USA
What is the way to create sheets names:Asia,Europe,USA?

2-Now the table start in each XLSX file in row 3 and in row 1 there is a title .

What is the way that table start in cell A1 and have no title?

3-I dont want to print on screen because in real life tables will be very big

what is the way to prevent prints on screen

thank you

Data cars;
set sashelp.cars;
label make='Firma' mode='model name' type='type of car';
Run;

proc sort data=cars ;by origin;run;


ods excel file="/path/eee.xlsx"
options(sheet_interval='bygroup');
proc print data=cars label noobs;
by origin;
run;
ods excel close;

[jimbarbour]

Hmmm.  Well, that's a bit tricky.  I know how to assign a sheet name in the options, but I'm not sure how it would work with an interval.

Here's how you would specify what the sheet name should be:

ODS	Excel	OPTIONS(Sheet_Name	=	"Asia");

Play with that a bit, and let me think about this some more.  Also, perhaps someone else may have a good idea here.

Jim

[jimbarbour · Accepted solution]

OK, here you go.  The code below will do what you want.  What I've done is added 

sheet_name='#byval1'

to the Options.  Give it a try.  It's working on my system.  

By the way, you have a typo in your Label statement in your Data step; "mode" should be "model".  The correct code is shown below.

Jim

Data cars;
set sashelp.cars;
label make='Firma' model='model name' type='type of car';
Run;

proc sort data=cars ;by origin;run;

ods excel file="C:\Users\jbarbour\Documents\SAS\Pgm\Training\Excel_By_Group_Names\Cars_By_Origin.xlsx"
options(sheet_interval='bygroup' sheet_name='#byval1');

proc print data=cars label noobs;
by origin;
run;
ods excel close;

[Ronein]

Thank you,
What abut the 2 other requests:
1-Data will be presewnted in XLSA sheet from cell A1 (Currently It start in row 3 in cell C1)
2-Data will be presented without title. Currently in First row of each XLSX sheet there is a title
3-I dont want to see print in SAS window table because in real life the cars data set is very big
May you show please the way to apply these 3 requests? thanks J

[jimbarbour]

OPTION NOBYLINES will suppress the BY Group title.  Once you remove that title, the table should start in cell A1. Try it and see if that's what you need.  I think that's what you mean.

Regarding not having anything go to the SAS results window, try ODS NORESULTS.

Jim

[PaigeMiller]

Turn off the title in your SAS code.

title;

Turn off HTML (or other output destination)

ods html close;