topic creating multisheet excel files by using proc report in ODS and Base Reporting

creating multisheet excel files by using proc report

russfern — Wed, 04 Apr 2018 09:22:48 GMT

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!

Re: creating multisheet excel files by using proc report

RW9 — Wed, 04 Apr 2018 10:14:34 GMT

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.

Re: creating multisheet excel files by using proc report

PaigeMiller — Wed, 04 Apr 2018 12:05:26 GMT

Also works with ODS EXCEL instead of ODS TAGSETS.EXCELXP

Re: creating multisheet excel files by using proc report

RW9 — Wed, 04 Apr 2018 12:07:31 GMT

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.

Re: creating multisheet excel files by using proc report

PaigeMiller — Wed, 04 Apr 2018 12:19:39 GMT

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.

Re: creating multisheet excel files by using proc report

Reeza — Wed, 04 Apr 2018 14:58:54 GMT

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;