topic Re: Export one data set into one XLSX file with multiple sheets-Automat BY in SAS Programming

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

Ronein — Thu, 10 Jun 2021 11:25:00 GMT

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;

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

jimbarbour — Thu, 10 Jun 2021 15:58:26 GMT

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

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

jimbarbour — Thu, 10 Jun 2021 16:24:57 GMT

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;

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

Ronein — Thu, 10 Jun 2021 16:42:04 GMT

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

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

jimbarbour — Thu, 10 Jun 2021 17:33:10 GMT

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

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

PaigeMiller — Thu, 10 Jun 2021 17:46:25 GMT

Turn off the title in your SAS code.

title;

Turn off HTML (or other output destination)

ods html close;