1
From SAS® to Excel via XML
Vincent DelGobbo, SAS Institute Inc., Cary, NC
ABSTRACT
Transferring data between SAS and Microsoft® Excel can be difficult, especially when SAS is not installed on a
Windows® platform. This paper discusses using new XML support in BASE SAS 9.1 software to move data between
SAS and Microsoft Excel (versions 2002 and later). You can use the techniques described here regardless of the
platform on which SAS software is installed, such as Windows, OpenVMS™, UNIX® or z/OS®. The use of SAS
server technology is also discussed.
INTRODUCTION
The techniques described in this paper are for people who either have not licensed SAS 9.1 SAS/ACCESS® to PC
Files, or those who want to display parts of SAS output in separate Excel worksheets.
Techniques currently exist for those in other situations:
•
Using SAS 9.1 SAS/ACCESS® to PC Files, you can import Excel data into SAS and write to Excel
workbooks from both Windows and UNIX environments (Plemmons, 2003). In previous releases of SAS,
access to Excel was only possible with Windows versions of SAS software.
•
If you want SAS output in a single Excel worksheet, you can use the Output Delivery System (ODS) to
generate an HTML file which you can then be opened with Excel (DelGobbo, 2003).
This paper and all source code are available on the SAS Presents Web site (http://support.sas.com/saspresents/).
WHAT IS XML?
XML is an acronym for Extensible Markup
Language, and represents a way to define
and format data for easy exchange. XML
is similar to HTML in that it uses tags.
Unlike HTML, whose tags control how data
is rendered, XML tags describe the
structure and meaning of data but do not
control how it is rendered.
For example, consider the XML file shown
in Figure 1. The file contains the make,
model name, and model year for several
vehicles. Note the lack of HTML tags such
as <TABLE>, <TR>, and <TD>. Instead,
well-structured XML tags are used to
describe the data. To find out more abo