Are you the lone-wolf SAS programmer in your company - managing the data, performing the analyses, and graphing the output for everyone else? And what's the only format they all know how to work with, and wish they had your output in? Let's face it ... that's probably an Excel spreadsheet!
Previously you had to jump through some hoops to send SAS graphs to an Excel spreadsheet, but now you're in luck! In SAS 9.4 (TS1M3), there's an easy new way to send your output (including graphs) to Excel. Directly to Excel! (Do not pass go, do not collect $200!)
Chris Hemedinger gave you a little preview of the experimental version of ODS Excel in SAS 9.4M1 and 9.4M2. And we now have the production version in 9.4M3! An astute user (Frank Tokic) actually brought this new feature to my attention, and provided an example of using it with SAS/Graph (thanks Frank!) I modified Frank's example to also demonstrate a few additional features, and I think it makes a great starting place for anyone interested in sending SAS output to Excel.
The example demonstrates:
- Sending a traditional SAS/Graph Gchart to an Excel spreadsheet.
- Sending a SAS Proc Print table to an Excel spreadsheet.
- Starting the table at a specific row & column.
- Including the title text above the table.
- Using date-formatted data in the table.
- Writing the graph and table to separate sheets.
- And controlling the names of the sheets.
Here are screen-captures showing the graph and table in the spreadsheet:
data my_data; input item $ 1-6 Amount Purchase_date date9.; datalines; ITEM A 11.8 14nov2016 ITEM B 10.5 01jul2016 ITEM C 8.8 22feb2015 ITEM D 6.8 01apr2012 ITEM E 3.9 03aug2016 ITEM F 2.3 02mar2016 ; run; ods listing close; ods excel file="excel_sas_graph.xlsx" style=htmlblue; goptions gunit=pct htitle=6 ftitle="albany amt/bold" htext=3.5 ftext="albany amt/bold" ctext=gray33; axis1 label=none value=(justify=right); axis2 label=('AMOUNT') order=(0 to 12 by 2) minor=none offset=(0,0); pattern v=solid color=dodgerblue; ods excel options(SHEET_NAME='Graph'); title1 ls=1.5 "Simple Bar Chart"; proc gchart data=my_data; format amount dollar5.0; hbar item / discrete type=sum sumvar=amount nostats maxis=axis1 raxis=axis2 noframe autoref clipref cref=graycc; run; ods excel options(SHEET_NAME='Data' EMBEDDED_TITLES='yes' START_AT='3,2'); title c=gray33 "Raw Data Values"; proc print data=my_data noobs label; label purchase_date='Purchase date'; format amount dollar5.2; format purchase_date date9.; run; quit; ods excel close;
Hopefully this example will get you started with the basics. ODS Excel is pretty new, and therefore I'm not an expert (yet), but hopefully the documentation will answer most of your questions. And if that doesn't satisfy all your curiosity, I'd recommend posting your question in the SAS community pages.