creating multisheet excel files by using proc report

[russfern]

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!

[RW9]

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]

Also works with ODS EXCEL instead of ODS TAGSETS.EXCELXP

[RW9]

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]

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.

[Reeza]

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;