From SASо to Excel via XML.pdfVIP

® 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 (/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, mod