Study guide
9.3 Exporting Reports to Excel with ODS EXCEL & XLSX
SAS Certified Specialist: Base Programming Using SAS 9.4 A00-231
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: SAS Base Programming Exam Overview
1.1 A00-231 Exam Format, Scoring & Test-Day Logistics 1.2 Official Content Areas, Weights & Study Plan
2 Chapter 2: Accessing & Creating SAS Data Structures
2.1 SAS Environment, Syntax Rules, & Data Step Basics 2.2 SAS Data Libraries & the LIBNAME Statement 2.3 Investigating Libraries with PROC CONTENTS & PROC DATASETS 2.4 Importing Delimited & Excel Data with PROC IMPORT 2.5 Raw Data Ingestion with INFILE and INPUT Statements
3 Chapter 3: Managing & Combining SAS Data Sets
3.1 Reading SAS Data Sets with the SET Statement 3.2 Combining Data Sets: Concatenating & Interleaving 3.3 Match-Merging with BY Statements & FIRST./LAST. Processing 3.4 Controlling Data Output & Variable Selection (KEEP, DROP, RENAME, OBS=)
4 Chapter 4: Data Transformation & Conditional Logic
4.1 Creating & Modifying Variables with Assignment Statements 4.2 Modifying Variable Attributes with LENGTH, LABEL, FORMAT & RENAME= 4.3 Conditional Processing: IF-THEN/ELSE Statements & WHERE Subsetting 4.4 Accumulating Totals & Subtotals (RETAIN & Sum Statement) 4.5 Processing Data Iteratively with DO Loops
5 Chapter 5: SAS Functions & Data Type Conversions
5.1 Character Functions for String Manipulation 5.2 Numeric & Mathematical Functions 5.3 Working with SAS Dates, Times, & Intervals 5.4 Reading & Displaying Dates with Informats and Formats 5.5 Explicit & Implicit Data Type Conversions (INPUT & PUT)
6 Chapter 6: Restructuring Data & Program Maintenance
6.1 SAS Array Processing for Multi-Variable Transformations 6.2 Sorting Data Sets with PROC SORT 6.3 Restructuring Data Sets with PROC TRANSPOSE 6.4 Creating & Applying Custom Formats with PROC FORMAT 6.5 Simplifying Programs with Macro Variables (%LET)
7 Chapter 7: Error Handling & Debugging SAS Programs
7.1 SAS Log Analysis & Diagnostic Messages 7.2 Syntax vs. Logic Errors & Phasing 7.3 Debugging Techniques with _ERROR_, _N_, & PUT Statements 7.4 Identifying Logic Errors with the PUTLOG Statement 7.5 Handling Missing Values & Preventing Calculation Propagations
8 Chapter 8: Summary & Detail Data Reporting
8.1 Detail Reporting with PROC PRINT 8.2 Descriptive Statistics with PROC MEANS & PROC SUMMARY 8.3 Frequency & Categorical Analysis with PROC FREQ 8.4 Screening Extreme & Missing Values with PROC UNIVARIATE 8.5 Custom Tabular Reports with PROC REPORT & PROC TABULATE
9 Chapter 9: ODS Output Delivery & Data Export
9.1 Output Delivery System (ODS) Architecture Basics 9.2 Generating PDF, HTML, & RTF Output Destinations 9.3 Exporting Reports to Excel with ODS EXCEL & XLSX 9.4 Generating CSV, PowerPoint & Other ODS Destinations 9.5 Customizing ODS Titles, Footnotes, & Global Options 9.6 Exporting SAS Data Sets with PROC EXPORT & the XLSX Engine
9.3 Exporting Reports to Excel with ODS EXCEL & XLSX
Key Takeaways Explain This Topic AI
The ODS EXCEL destination generates native Microsoft Excel (.xlsx) workbooks supporting multiple worksheets, embedded SAS styles, and formula options.
Individual worksheets within an Excel workbook can be customized using ODS EXCEL OPTIONS(SHEET_NAME='Name') prior to running procedure steps.
Key ODS EXCEL sub-options include EMBEDDED_TITLES='YES', AUTOFILTER='YES', GRIDLINES='YES', and FROZEN_HEADERS='YES'.
LIBNAME XLSX connects SAS directly to an Excel file as if it were a SAS data library, allowing direct DATA step reads and writes without ODS styling overhead.
ODS EXCEL produces styled, formatted spreadsheet reports, whereas LIBNAME XLSX and PROC EXPORT focus on raw data table transfers.
Last updated: August 2026
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.
Method Primary Purpose File Format Supports ODS Styles/Titles? Requires SAS/ACCESS to PC Files?
ODS EXCEL Formatted report creation with multiple tabs, colors, and titles. Native .xlsx Yes (Full ODS Style support) No (Built into Base SAS)
LIBNAME XLSX Direct reading/writing of raw data tables to Excel sheets via DATA step. Native .xlsx No (Raw data tables only) No (Built into Base SAS)
PROC EXPORT Exporting a single SAS data set to CSV, XLS, or XLSX. .csv , .xlsx No (Raw data output) Depends on engine/DBMS specified
ODS HTML (.xls) Legacy trick saving HTML table code with a .xls extension. HTML formatted Limited (Generates Excel warnings) No (Obsolete method)
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
Sub-Option Valid Values Description
SHEET_NAME= "Text" Sets the explicit name of the active Excel worksheet tab.
EMBEDDED_TITLES= "YES" | "NO" Controls whether SAS TITLE statements appear inside Excel worksheet cells.
EMBEDDED_FOOTNOTES= "YES" | "NO" Controls whether SAS FOOTNOTE statements appear in Excel worksheet cells.
GRIDLINES= "YES" | "NO" Toggles visible cell gridlines on or off in the Excel sheet.
AUTOFILTER= "YES" | "NO" | "ALL" Enables Excel drop-down column filtering headers on procedure output.
FROZEN_HEADERS= "YES" | "OFF" | "1" Freezes table header rows so they remain visible when scrolling down.
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";)