The SAS Output Delivery System and reporting techniques

ODS excel & multiple sheets

Accepted Solution Solved
Reply
Highlighted
Frequent Contributor
Posts: 92
Accepted Solution

ODS excel & multiple sheets

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;


Accepted Solutions
Solution
‎12-15-2016 09:00 PM
SAS Super FREQ
Posts: 8,957

Re: ODS excel & multiple sheets

[ Edited ]
Posted in reply to Niugg2010

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

mult_names.png

View solution in original post


All Replies
Frequent Contributor
Posts: 96

Re: ODS excel & multiple sheets

Posted in reply to Niugg2010
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#p09n5pw9ol0897n1qe04...

 

Frequent Contributor
Posts: 92

Re: ODS excel & multiple sheets

Posted in reply to Kalind_Patel

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

 

 

Frequent Contributor
Posts: 96

Re: ODS excel & multiple sheets

Posted in reply to Niugg2010

Can you provide the code,

which you have submitted ?

 

Solution
‎12-15-2016 09:00 PM
SAS Super FREQ
Posts: 8,957

Re: ODS excel & multiple sheets

[ Edited ]
Posted in reply to Niugg2010

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

mult_names.png

New User
Posts: 1

Re: ODS excel & multiple sheets

Posted in reply to Cynthia_sas

Awesome!  Thanks Cynthia.  

New Contributor
Posts: 3

Re: ODS excel & multiple sheets

Posted in reply to Kalind_Patel
ods excel file="YOURS.xlsx" 
   options(sheet_label="Column" );
New Contributor
Posts: 2

Re: ODS excel & multiple sheets

Posted in reply to BoelterBI
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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1233 views
  • 1 like
  • 6 in conversation