9.3 Exporting Reports to Excel with ODS EXCEL & XLSX

9.3 Exporting Reports to Excel with ODS EXCEL & XLSX

Exporting reporting data to Microsoft Excel is one of the most common tasks required of SAS programmers in corporate environments. SAS provides multiple mechanisms to create Excel files, ranging from styled reporting destinations (ODS EXCEL) to direct data engine connectors (LIBNAME XLSX) and procedural data transfer (PROC EXPORT).

---

Comparing SAS Excel Export Methods

Understanding the fundamental differences between these methods is essential for both daily SAS programming and the SAS Base Certification exam.

[!CAUTION] In older SAS codebases, you may encounter ODS HTML FILE="report.xls";. This creates an HTML file with an Excel extension, causing Microsoft Excel to display a file format warning upon opening. Modern SAS applications should always use ODS EXCEL or LIBNAME XLSX to produce genuine OpenXML .xlsx files.

---

The ODS EXCEL Destination

The ODS EXCEL destination is the standard Base SAS solution for producing formatted native Excel spreadsheets.

Basic ODS EXCEL Syntax Lifecycle

ods excel file="C:\\reports\\QuarterlySales.xlsx"
          style=Excel
          options(sheet_name="Q1 Summary" 
                  embedded_titles="YES"
                  gridlines="YES");

title "Q1 Regional Sales Performance";
proc report data=sashelp.shoes;
   column Region Sales Profit;
   define Region / group;
run;

ods excel close;

---

Managing Worksheets with SHEET_NAME and Sheet Breaks

By default, ODS EXCEL places procedure output sequentially into worksheets. You can control worksheet naming and placement using the SHEET_NAME= option inside ODS EXCEL OPTIONS().

Creating Multi-Tab Worksheets

To create a workbook with multiple tabs, update SHEET_NAME= before each procedure step:

ods excel file="C:\\reports\\MultiTabReport.xlsx" style=HTMLBlue;

/* Tab 1: Class Roster */
ods excel options(sheet_name="Student Roster");
proc print data=sashelp.class;
run;

/* Tab 2: Summary Statistics */
ods excel options(sheet_name="Summary Metrics");
proc means data=sashelp.class mean std;
   var Height Weight;
run;

/* Tab 3: Detailed Data */
ods excel options(sheet_name="Detailed Data");
proc print data=sashelp.cars(obs=10);
run;

ods excel close;

Key ODS EXCEL Sub-Options Table

---

Direct Data Access with LIBNAME XLSX

While ODS EXCEL is designed for formatted reports, the LIBNAME XLSX engine treats an Excel .xlsx file directly as a SAS data library. Each worksheet in the Excel workbook corresponds to a SAS data set within that library.

Syntax for LIBNAME XLSX

LIBNAME libref XLSX "physical-path-to-excel-file.xlsx";

Example: Writing SAS Data Sets directly to Excel Worksheets

/* Assign the XLSX library engine */
libname myxl xlsx "C:\\data\\CompanyData.xlsx";

/* Create Sheet 1 named 'Employees' using DATA Step */
data myxl.Employees;
   set sashelp.class;
run;

/* Create Sheet 2 named 'Cars' using PROC COPY */
proc copy in=sashelp out=myxl;
   select cars;
run;

/* Clear the libref to close and unlock the Excel file */
libname myxl clear;

Key Characteristics of LIBNAME XLSX

• No SAS/ACCESS License Required: Included directly in Base SAS 9.4M2 and later.
• Fast Execution: Bypasses ODS formatting pipelines, transferring raw binary data directly into XML tables.
• Data Step Compatibility: You can use standard DATA step statements, PROC DATASETS, PROC COPY, and PROC SQL against Excel worksheets.
• Sheet Names as Member Names: Worksheets are accessed using libref.SheetName.

---

ODS EXCEL vs. LIBNAME XLSX Decision Matrix

                      Do you need report formatting, 
                       styles, titles, or colors?
                                   |
                  +----------------+----------------+
                  |                                 |
                 YES                                NO
                  |                                 |
                  v                                 v
        Use ODS EXCEL Destination               Is this a raw data transfer
     (ODS EXCEL FILE="path.xlsx";)             between SAS & Excel tables?
                                                    |
                                                    v
                                          Use LIBNAME XLSX Engine
                                       (LIBNAME myxl XLSX "path.xlsx";)