BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Niugg2010
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

7 REPLIES 7
Kalind_Patel
Lapis Lazuli | Level 10
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...

 

Niugg2010
Obsidian | Level 7

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

 

 

Kalind_Patel
Lapis Lazuli | Level 10

Can you provide the code,

which you have submitted ?

 

Cynthia_sas
SAS Super FREQ

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

mhlundgren
Calcite | Level 5

Awesome!  Thanks Cynthia.  

BoelterBI
Calcite | Level 5
ods excel file="YOURS.xlsx" 
   options(sheet_label="Column" );
jgcasatejada
Calcite | Level 5
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 7258 views
  • 3 likes
  • 6 in conversation