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!
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.
Also works with ODS EXCEL instead of ODS TAGSETS.EXCELXP
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.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.