Paper SIB-105 - Customized Excel Output Using the Excel Libname

Sep 11, 2026

Paper SIB-105 explains how the SAS EXCEL libname engine enables customized Excel output by writing SAS data into pre-defined named ranges inside an existing, tailored Excel worksheet. It contrasts this approach with Dynamic Data Exchange (DDE), highlighting DDE’s complexity, limited error handling, and the need to run Excel on the client machine. A step-by-step workflow covers layout design, range definition, data reshaping, and populating the report through libname.

LanguageEnglish

CategoryPresentations

FormatPDF

Pages11

Reading Time4 min

AccessFree to download

Introduction

ExcelXP and earlier SAS-to-Excel methods

Why use the Excel libname engine

Procedure for Customized Excel Output

Create Excel layout

Define receiving range

Massage SAS data

Populate via Excel libname

EXCEL LIBNAME

Basic syntax and workbook creation

Working with librefs and named ranges

Writing SAS Data to Excel

Populating worksheets and verifying created entities

Excel Libname Limitations

Paper SIB-105 Customized Excel Output Using the Excel Libname Harry Droogendyk, Stratia Consulting Inc. , Lynden , ON ABSTRACT The advent of the ODS ExcelXP tagset and its many features has afforded the SAS© user great flexibility in creating publishable Excel output. But what if the report must be delivered by populating pre-defined, customized Excel worksheets? DDE has been around for ages, and is a viable option, but often not a simple one to execute. This paper will demonstrate another method, the EXCEL libname engine. Using the EXCEL libname, SAS is able to write data into specific, pre-defined“named ranges” in customized Excel worksheets. INTRODUCTION There has always been ways to move SAS data to Excel , even if it was as simple as opening a SAS-created delimited text file in Excel. Since Excel quite happily opens HTML files, as enhancements were made to the SAS System , users began to utilize ODS HTML ( or data step PUTs ) to create customized output. When Excel XP/2003 added support for XML, an entirely new range of possibilities opened up with the creation of the ever-improving ExcelXP tagset. Any of the methods already mentioned work well for displaying tabular data. Until the inclusion of the Excel libname in the SAS Access for PC Files software, if the Excel output required special or heavily customized formatting, the only choice was Dynamic Data Exchange, or DDE. While DDE is very powerful , it has a number of significant drawbacks. For anything beyond the simplest operations, DDE can be syntactically difficult, and worse, provides only rudimentary error handling and reporting when things go awry. In addition, Excel must actually be invoked on the machine, tying up the PC while the spreadsheet is manipulated / populated by DDE. The SAS 9 Excel Libname statement allows the pushing and pulling of SAS data to/from Excel , even if Excel is not installed on the machine running SAS. This paper will only focus on creating Excel output using the libname engine. See the References section at the end of the paper for additional information on pulling data from Excel into SAS. The procedure for creating customized Excel Output using SAS’s Excel libname engine: 1. Create a customized report layout in Excel 2. Define a range in Excel to receive the SAS data 3. Process the SAS data and massage it into the shape of the range 4. Use the Excel libname to load the Excel range and populate the report. EXCEL LIBNAME The Excel libname syntax is quite familiar: libname xls physical-file-name ; eg . libname xls excel 'c:\temp\report .xls' ; … SAS code … libname xls clear ; * very important !! ; If the physical file specified does not exist, SAS will create a workbook of that name. Once the libname statement is successfully executed, the Excel workbook and the sheets and named ranges defined in the workbook will be available in the SAS Explorer window. The library can be treated like any other SAS libref with a couple of caveats: 􀂷 row and column limitations as defined by Excel , eg. 64K rows, 256 columns for Excel 2003 􀂷 Excel sheet and column names can contain characters not normally considered valid by SAS, use of options validvarname=any may be necessary when reading from Excel using the libname. To reference Excel sheet names and named ranges, the familiar libref.dataset syntax is used ( sheet names are suffixed with $ , necessitating the use of SAS Name Literals syntax, ie. ‘sheet1$’n when referencing ) . eg: data xls . 'sheet1$'n; * referencing worksheet sheet1 ; data xls .range_out; * referencing named range range_out; WRITING SAS DATA TO EXCEL To populate Excel spreadsheets with the libname engine, most any DATA or PROC step can be used, eg. data xls .class; set sashelp .class; run; proc copy in = sashelp out = xls; select prdsale shoes; run; After the DATA and PROC COPY steps are run, the SAS Explorer can be used to display the contents of

FAQs

What problem does the Excel libname engine solve compared with DDE?

It provides a method to push SAS data into customized Excel worksheets using pre-defined named ranges without the extra syntactic difficulty, weak error handling, and Excel client requirement commonly associated with DDE.

How is SAS data mapped into a customized Excel report when using the Excel libname?

SAS writes data into specific named ranges within the target workbook. The workflow is: create the Excel layout, define the receiving range, reshape the SAS data to match the range structure, then load and populate via the Excel libname.

What are the key limitations of the Excel libname compared with DDE?

The paper notes that Excel libname cannot create new workbooks or sheets and named ranges, cannot apply formatting like font and color, and has restrictions such as not being able to delete formulas-containing cells or append data in ways DDE may support.

Sep 11, 2026

Uploaded by Riley West