topic Re: ODS excel & multiple sheets in ODS and Base Reporting

ODS excel & multiple sheets

Niugg2010 — Thu, 15 Dec 2016 05:06:05 GMT

Below is my code, I want to get a excel files with multiple sheets. My question is how I can specify each sheet name. For example: sheet 1 is named Report, sheet2 is named RESULT, and sheet 3 is named FINDING.

data a;
length PatientID $2;
input PatientID $ Systolic Diastolic @@;
datalines;
CK 120 50 SS 96 60 FR 100 70
CP 120 75 BL 140 90 ES 120 70

;

data b;
length PatientID $2;
input PatientID $ Systolic Diastolic @@;
datalines;
CP 165 110 JI 110 40 MC 119 66
FC 125 76 RW 133 60 KD 108 54
DS 110 50 JW 130 80 BH 120 65

;
data c;
length PatientID $2;
input PatientID $ Systolic Diastolic @@;
datalines;
CP 120 75 BL 140 90 ES 120 70
CP 165 110 JI 110 40 MC 119 66
FC 125 76 RW 133 60 KD 108 54
DS 110 50 JW 130 80 BH 120 65

;

ods excel file="path\aaa.xlsx";
proc report data=a;
column _all_;
run;
proc report data=b;
column _all_;
run;
proc report data=c;
column _all_;
run;
ods excel close;

Re: ODS excel & multiple sheets

Kalind_Patel — Thu, 15 Dec 2016 06:46:00 GMT

ods excel file="name.xlsx" 
   options(sheet_label="country" );

try to use this option,

it will create an sheet with the name which you have mentioned.

for more info please refer this link:

http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04zeur27rv.htm

Re: ODS excel & multiple sheets

Niugg2010 — Thu, 15 Dec 2016 11:17:23 GMT

When I run the code I got the sheet names as following:

"country - Detailed and-or su 1"

"country - Detailed and-or su 2"

"country - Detailed and-or su 3".

This is not what I want.

I want differnt sheet names such as "Report", "Result", "Finding".

Re: ODS excel & multiple sheets

Kalind_Patel — Thu, 15 Dec 2016 11:34:11 GMT

Can you provide the code,

which you have submitted ?

Re: ODS excel & multiple sheets

Cynthia_sas — Thu, 15 Dec 2016 18:07:16 GMT

My suggestion would be to use the sheet_name option instead of the sheet_label option.

See the attached example. It doesn't matter whether you use PROC PRINT or PROC REPORT or PROC ... Sheet_name will change the sheet_name.

cynthia

Re: ODS excel & multiple sheets

BoelterBI — Fri, 03 Mar 2017 20:04:38 GMT

ods excel file="YOURS.xlsx" 
   options(sheet_label="Column" );

Re: ODS excel & multiple sheets

mhlundgren — Wed, 27 Dec 2017 22:39:03 GMT

Awesome! Thanks Cynthia.

Re: ODS excel & multiple sheets

jgcasatejada — Sat, 06 Jan 2018 22:18:52 GMT

Hi All,
Sometimes there are some ways to get the same goal,
I Will show you other way to get It.
You can use a new library to Excel file with

LIBNAME MyExcel XLSX "path\name.xlsx";

Then if you have a dataset named "Wombat", you only must to do a data step

DATA MyExcel.Wombat;
SET Work.Wombat;
RUN;

And you can append all sheets that you need with only a data step.

If you any ods in excel, you could merge all in an only Excel file with Excel libraries

Finally you must disconnect all Excel file with.

LIBNAME MyExcel CLEAR;