Paper 2182-2015 - Using the SAS Output Delivery System (ODS) and the TEMPLATE Procedure to Replace Dynamic Data Exchange (DDE)

Sep 12, 2026

Many SAS papers explain how to use Dynamic Data Exchange (DDE) to move data from SAS to Excel. This paper demonstrates an alternative approach using the SAS Output Delivery System (ODS) together with the TEMPLATE procedure, focusing on producing publication-standard Excel workbooks from SAS output. It walks through practical formatting techniques for Excel—numeric formats, font effects such as superscripting, cell borders, and text justification—using STYLE elements built with PROC TEMPLATE.

LanguageEnglish

CategoryPresentations

FormatPDF

Pages9

Reading Time3 min

AccessFree to download

INTRODUCTION

USING THE TEMPLATE PROCEDURE

FORMATING NUMBERS

FORMATING FONTS

MORE STYLE ELEMENTS

FORMATING CELLS

FORMATING JUSTIFICATION OF FONTS

Paper 2182-2015 Using the SAS® Output Delivery System (ODS) and the TEMPLATE Procedure to Replace Dynamic Data Exchange (DDE) Peter Timusk, Centre for Special Business Projects, Statistics Canada ABSTRACT Many papers have been written over the years that describe how to use Dynamic Data Exchange (DDE) to pass data from SAS® to Excel. This presentation aims to show you how to do the same exchange with the SAS Output Delivery System (ODS) and the TEMPLATE Procedure. INTRODUCTION This paper demonstrates techniques using the TEMPLATE procedure and the STYLE element to format Excel workbooks output with SAS Output Delivery System (ODS) . There have been many papers over the years explaining how to format Excel workbooks using the Dynamic Data Exchange (DDE) method of communicating with Excel from SAS. The author has often found these papers online and used these DDE techniques to deliver publication standard Excel workbooks containing statistics from Statistics Canada surveys. Clients prefer Excel workbooks and in using Excel templates these clients have an ability to define statistical estimates and population and sub-population requirements. The production programmer has only to calculate the statistics in SAS and then place the results in the client’s Excel template for publication as an Excel workbook. More SAS user papers these days show how to use ODS and PROC TEMPLATE to achieve the same formatting results with Excel workbooks. This paper contributes to these papers and will demonstrate some formatting techniques important in the author’s programming statistics for publication in Excel workbooks. It is hoped that future SAS programmers will find many published example papers for using ODS, as we can now find for DDE. USING THE TEMPLATE PROCEDURE Throughout we will show a simple PROC TEMPLATE and a DEFINE STYLE to make a new STYLE element using various STYLE attributes to format Excel. This is the technique and code used by, Nate Derby and Colleen McGahan in their 2013 SAS Global Forum paper: Maintaining Formats when Exporting Data from SAS® into Microsoft® Excel®, also with the Class dataset. We will first apply a numeric format as these authors did, then extend this to a font format, then a cell format and finally a font justification format. Then we will use all of these together to end the examples. For many more possible formats see the references section. FORMATING NUMBERS Here we use PROC TEMPLATE and a STYLE element to format two variables height and weight from the Class dataset with the STYLE element “data_num” to display two decimal places in the Excel output: proc template; define style styles.mystyle; parent = styles.default; style data_num from data / tagattr= 'format:0 . 00' ; end; quit; ods tagsets.excelxp file="&folder output from excelxp, numeric formatting with proc templatenumberformat.xls" style=mystyle ; proc print data=sashelp.class; var name sex age ; var height weight / style ( data )=data_num; run; ods tagsets.excelxp close ; (NOTE: the code in this paper is drawn from examples in (Derby & McGahan, 2013),(DelGobbo, 2012) and SAS documentation and then extended to other formats.) The format used is set using a tagattr and this string 'format:0 .00' Display 1 Excel with Numeric Formatting FORMATING FONTS Here we use the PROC TEMPLATE and a STYLE element to format the variable age from the Class dataset with the STYLE element “super_up” to appear as superscripted data points in the Excel output: proc template; define style styles.mystyle; parent = styles.default; style super_up from data / fontsize= 8pt verticalalign=top; end; quit; ods tagsets.excelxp file="&folder output from excelxp, font formatting with proc templatesuperup.xls" style=mystyle ; proc print data=sashelp.class; var name sex; var age / style ( data )=super_up; run; ods tagsets.excelxp close ; Superscripting using the attribute “super” seems possible in a STYLE

FAQs

What problem does the paper address compared with Dynamic Data Exchange (DDE)?

It replaces the DDE-based method of exchanging data from SAS to Excel by using SAS Output Delivery System (ODS) and the TEMPLATE procedure to achieve equivalent Excel output formatting.

How does PROC TEMPLATE format numbers in the Excel output?

It defines a STYLE element (for example, using a tag attribute format string) and applies that style to selected variables so the exported Excel values display with the desired decimal places.

What kinds of Excel formatting are covered besides numeric formats?

The paper covers font formatting (including superscript-like display), cell borders, and font justification for exported values.

Sep 12, 2026

Upload by วิน