04-04-2018 05:22 AM
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?
datasets: abc, def, ghi, jkl etc...
04-04-2018 06:14 AM
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.
04-04-2018 08:07 AM
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.
04-04-2018 08:19 AM
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.
04-04-2018 10:58 AM
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;