I Want to Put My SAS Output into an Excel Spreadsheet

You have SAS output. It looks rather stark and unfriendly in the listing. You want to create a friendly looking Excel spreadsheet. How can you do this easily and quickly?

One of the easiest ways of creating an Excel file from SAS is to use ODS ExcelXP.tagset.

Using ODS to Send SAS Output to an Excel File

ODS ExcelXP tagset is a reliable method of creating formatted Excel tables in SAS. ExcelXP doesn’t create native XLS or XLSX files; instead it creates a file in XML format. This means while the file created via ODS opens in Excel, it is also very large. However after you open the file in Excel and save the file as an XLSX file, the size shrinks dramatically.

Using ODS is fairly straightforward. ODS simply redirects the output to a file. If you do not want the output in the listing file, you can turn that off. ODS LISTING CLOSE will turn off the listing. ODS LISTING will turn the listing back on.

ODS LISTING Close

Using ExcelXP Tagset Options

This document provides a tip sheet of options in EXCELXP.

Example:

In this example, the code creates an Excel file, HF_drugs.xls using the analysis style. It names the tab or sheet, Heart Failure Drugs. The output, a simple PROC PRINT, prints out the brand name and other information from the drug dictionary.

ODS LISTING Close Proc Print

I Need Leading Zeroes in My Excel Table. What Happened to My Leading Zeroes?

You will need to use a combination of SAS and Excel formats to display the data the way you want. Excel takes away leading zeros when it imports files, so it is not enough to tell SAS to print a formatted version of the variable such as NDC code. You must also tell Excel to display it with leading zeros as well.

In the following example, we want NDC_11 to print as an eleven-character number with leading zeroes. We need to include the SAS format:

format ndc_11 z11.;

to tell SAS we want the leading zeroes. Unfortunately, when you open the output HF_NDCS.xls in Excel, Excel will automatically strip the leading zeroes off numbers. To tell Excel you want leading zeroes on NDC_11, you need to use an Excel format.

How to Retain Leading Zeros in Your Excel Table:

ODS LISTING Close Leading Zeros

Output in Excel:

NDC_11BNGNN60
00002010102 AMMONIUM CHLORIDE AMMONIUM CHLORIDE
00002010402 PANCREATIN AMYLASE/LIPASE/PROTEASE/PANCREATIN
00002010403 PANCREATIN AMYLASE/LIPASE/PROTEASE/PANCREATIN
00002010502 POTASSIUM CHLORIDE POTASSIUM CHLORIDE
00002010504 POTASSIUM CHLORIDE POTASSIUM CHLORIDE
00002010602 POTASSIUM IODIDE POTASSIUM IODIDE
00002010902 SODIUM CHLORIDE SODIUM CHLORIDE
00002011002 SODIUM SALICYLATE SODIUM SALICYLATE
00002011102 SODIUM SALICYLATE SODIUM SALICYLATE
00002011104 SODIUM SALICYLATE SODIUM SALICYLATE

I Want to Output Multiple Sheets to an Excel Workbook. Is There a Way to Do That?

Using ODS to Create a Multi-sheet Excel Workbook:

With ods tagsets.ExcelXP, by default, each procedure is automatically sent to another sheet. If you want to name the worksheets, use the ods option (sheet_name=) to set the name of the worksheet:

ODS LISTING Close Multi-Sheet Excel Workbook

An additional reference is available here: http://www2.sas.com/proceedings/sugi31/115-31.pdf.