Use PROC EXPORT to Write SAS Data to External Files and Excel Workbooks - Exchanging Data Between SAS and Microsoft Excel - page 19

Chapter 2: Use PROC EXPORT to Write SAS Data
to External Files and Excel Workbooks
2.1 Introduction................................................................................................... 19
2.2 Purpose ......................................................................................................... 20
2.3 Syntax of the SAS EXPORT Procedure........................................................... 20
2.4 Data Access Methods for Excel Files Supported by PROC EXPORT .............. 21
2.5 Overview of the Examples ............................................................................. 22
2.6 List of Examples............................................................................................ 23
Example 2.1 SAS Code to Export Data to an Excel 4 or Excel 5 Format File ................23
Example 2.2 PROC EXPORT Using the DBMS=DLM Option...........................................24
Example 2.3 PROC EXPORT Using the DBMS=EXCEL Option .......................................25
Example 2.4 PROC EXPORT Using the DBMS=EXCELCS Option ..................................27
Example 2.5 SAS Code to Export Data to an Excel File with No Column Headers ......28
Example 2.6 SAS Code to Export Data to a Network Windows Computer ...................28
2.7 Conclusion .................................................................................................... 29
2.1 Introduction
This chapter will explain the syntax, usage, and the results that can be generated when using the SAS
EXPORT procedure. Examples will range from simple to complex. This chapter builds upon the Chapter 1
explanation and examples of the SAS Export Wizard.
As we saw in the last chapter, the SAS Export Wizard optionally can create a SAS code file of the PROC
EXPORT instructions used to write the output file. The SAS Export Wizard can execute SAS code to
output a SAS dataset in various formats. The code generated by the wizard can be saved, modified, and
reused. A SAS user can also write code to execute PROC EXPORT directly. The syntax of PROC
EXPORT will be explained and the options listed below in a table. PROC EXPORT is a general purpose
routine and is able to output data from SAS files in several different formats.
Because PROC EXPORT can write many of the file formats that Microsoft Excel can read directly, several
examples will be shown that write to files that can be loaded into Excel worksheets. But, because the focus
of this work is moving data from SAS to Excel and back, only options relative to creating Excel files will
be explored. Some of the code examined here was shown in Chapter 1, but in this chapter we will examine
the PROC EXPORT syntax in detail to show the flexibility of the SAS EXPORT procedure. One important
aspect of PROC EXPORT is its ability to interface with an external Data Base Management System
(DBMS). PROC EXPORT has a syntax argument called DBMS that makes this option available and
permits access to many different output data formats. We will focus on the ability to interface with different
versions of Excel.