ODS Excel – saving results directly into an Excel spreadsheet

I think it is very safe for me to say that most of us use Excel to enter our data.  We may need to  massage it to bring it into SAS for analysis, but that’s what we do.

So now let’s sit back and think….  how many times have you wanted to move your analysis results back into Excel?  Have you ever re-entered some SAS analysis results back into Excel?  Maybe to plot out your results? Or maybe to create a table of your results?  I suspect many of us have done this at one time or another.  Let me show you a quick way to accomplish this, without re-typing all those numbers!

ODS – Output Delivery System – we’ve seen this before.  You may have used it to save your output as a PDF file, or as a Word (RTF) file.  Well, there is also an Excel option here.  The general form of the statement is:

ods excel file=”   ” options(….);
…
ods excel close;

Looks a littler familiar right?  Let’s work through an example to see how this works.  I am using a small dataset where I would like to calculate the means and standard errors for each treatment group in the data, save my results in an Excel spreadsheet so I can use them to create a bar graph in Excel.

Data excel_test;
input trmt$ height;
datalines;
a 14
a 15
a 12
a 15
b 22
b 27
b 28
b 25
c 14
c 15
c 16
c 18
d 31
d 42
d 24
d 29
;
Run;

The data contains 4 treatments. To calculate the means and standard errors by treatment I will used the following piece of code:

Proc means data=excel_test mean stderr;
class trmt;
var height;
Run;

For more information on building this code, please review Proc MEANS blogpost

When I run this syntax I will get a table with the means and standard errors for each of the 4 treatments – perfect! Now, this is not a lot of information, so I could go ahead and retype these numbers in a blank Excel spreadsheet, but I am not a great typer, so let’s add the ODS Excel code.

ods excel file=”C:\Users\edwardsm\Documents\Workshops\SASsyFridays\20180119\height_means.xlsx” options(
sheet_name=”Summary”
embedded_titles=”yes”);

Proc means data=excel_test mean stderr;
class trmt;
var height;
title “Height means and standard errors”;
run;

ods excel close;

The Proc MEANS code is the same as above – no changes here! We simply tell SAS that we want the results of our Proc MEANS to be saved as an Excel file, by wrapping the code with the ODS Excel and ODS Excel Close code.

ods excel file=”C:\Users\edwardsm\Documents\Workshops\SASsyFridays\20180119\height_means.xlsx” options(
sheet_name=”Summary”
embedded_titles=”yes”);

ods excel – tells SAS that we want to use Excel as our output format.
file=”C:\Users\edwardsm\Documents\Workshops\SASsyFridays\20180119\height_means.xlsx” – tells SAS where the file should be saved and what to call it. Once I run this code, I should have a file called height_means.xlsx located in my Workshops\SASsyFridays\20180119 directory.

Then I have added a couple of options. Please note that there are a LOT of options you can use here, I have chosen to use the basic ones. We can do another session later in the year on other available options, if there is interest.

The first option is sheet_name = “Summary” – this is where I tell SAS what I want the Excel worksheet to be called. In this case I have selected Summary. This can be anything you want.

The second option is embedded_titles=”yes” – this allows SAS to send the titles from the Proc MEANS output to Excel. So you will see the title that I have provided for the Procedure as well as the titles from the Means output.

When you run this code, it will open Excel and the file that was created. It will also provide the output in the SAS Results Viewer.

After the Proc MEANS, I need to close the Excel file by adding the ODS Excel Close statement.

Now, you can use the information in the Excel file and create your graph or your table.