SAS to Excel

Ah yes – that lovely word Excel 😉 Many of my students enter their data into Excel – fair. I do it too, so easy to see where and how to enter my data in a spreadsheet format. Then we ensure that the data is clean before we bring it into SAS to run our lovely models and analyses. Get these beautiful result tables in SAS and then folks want to save those into Excel. I’ve talked about how we can bring our SAS results into an Excel format in a previous post.

But…. the next step that some of you may want to do.. is to bring the dataset you were working with in SAS back into Excel. While working with our data in SAS, we may have created new variables, or maybe we subsetted our data, or maybe we really want to save those residuals we were using to test our models with? There are several reasons you may want to save your data from SAS back into Excel.

Believe it of not, it’s pretty much the same coding we used when saving our outputs, but… you may be asking – How do I see my data? and how do I know what data I’m saving into Excel? Well… let’s turn that question around a bit – while working in SAS, how do you look at your data? Why – PROC PRINT of course!

Quickest and easiest way to save your data from SAS into Excel is as follows:

ods excel file=”C:\Users\edwardsm\Documents\Workshops\SAS\beef_weight_data.xlsx”;
Proc print data=beef_weight;
ods excel close;

ods excel = telling SAS that the output coming up you want to save it as an Excel file

file=” …” = giving a location on your computer where you want to save the file along with a file name – ending with .xlsx

ods excel close; = closing and saving the information into that Excel file.

Now I’m not going to talk about how to make that Excel file fancy – this is a quick and easy way to save your data back into Excel. Here’s a small example:

SAS code to save data as an Excel file
Resulting Excel file

Hope this helps out – stay tuned next week for another exciting little tip.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s