BookmarkSubscribeRSS Feed
russfern
Calcite | Level 5

I am using SAS EG 7.1 and have Excel 2016.

 

I need to use proc report to export multiple SAS datasets from a permanent sas library into a single excel workbook; with each dataset being printed on a new sheet. Also i need to give specific names to the sheets.

 

how can i achieve this using ods excel?

 

eg:library sasdata

datasets: abc, def, ghi, jkl etc...

 

output: Filename.xlsx

sheet1(name-task): abc

sheet2(name-outcome) :def

sheet3(name-issues): ghi......etc...

 

Please help!

 

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

And what have you searched for?  Reason is this is covered many many times in various ways.  You could use ods tagsets.excelxp (my preferred method):

ods tagsets.excelxp file="want.xml" options(sheet_name="ABC");

proc report...;
run;

ods tagsets.excelxp options(sheet_name="DEF");

proc report...;
run;

ods tagsets.excelxp close;

That keeps the best formatting from report.

PaigeMiller
Diamond | Level 26

Also works with ODS EXCEL instead of ODS TAGSETS.EXCELXP

--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Good point, thanks.  Is it fully stable yet?  I didn't for instance want to use libname excel as have had issues with the more recent technologies regarding Excel.

PaigeMiller
Diamond | Level 26

I have not really tested it via LIBNAME EXCEL.

 

ODS EXCEL seems to do everything I want, SAS is pushing it as the latest-and-greatest (which I believe), and there are lots of papers written on it with great examples.

 

And there was one glitch where text didn't seem to word-wrap properly, which this thread provides a solution to: https://communities.sas.com/t5/ODS-and-Base-Reporting/Lines-Wrapping-in-ODS-Excel/m-p/154932#M11794. But maybe SAS has fixed this, I don't know.

--
Paige Miller
Reeza
Super User

ODS EXCEL is stable/production as of SAS 9.4 TS1M3

 

This will generate an excel workbook called demo, with one sheet for each age, labeled Age - . 

You can control the sheet names by using the sheet_label and sheet_name options as desired. 

See the documentation for further specifications.

 

proc sort data=sashelp.class out=class; by age;run;

ods excel file='C:/_localdata/demo.xlsx' 
    style=meadow 
    options (sheet_interval="bygroup" sheet_label='Age');

proc report data=class; 
by age;
run;

ods excel close;

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1672 views
  • 2 likes
  • 4 in conversation