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.
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.
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:
Output in Excel:
| NDC_11 | BN | GNN60 |
|---|---|---|
| 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:
An additional reference is available here: http://www2.sas.com/proceedings/sugi31/115-31.pdf.