SAS Institute. The Power to Know

COMMUNITY

Read DelGobbo's SUGI papers

Learn more about user groups

News & Resources

Creating AND Importing Multi-Sheet Excel Workbooks the Easy Way with SAS

SAS software developer Vince DelGobbo has written another user-friendly paper on a topic that is important to many SAS users—moving data between SAS and Microsoft Excel. "Creating AND Importing Multi-Sheet Excel Workbooks the Easy Way with SAS" discusses how you can take advantage of the XML support in Base SAS 9.1 and Microsoft Excel 2002 (Excel XP) or later. DelGobbo's paper was well received at SUGI 31 and PharmaSUG 2006, and he has been invited to many SAS user group meetings to talk about the process.

"Because so many SAS users need to move their SAS output into Excel we wanted to come up with a simple method that anyone could use," said DelGobbo. He worked with SAS developer Eric Gebhart to create the ExcelXP ODS tagset, which is used to export SAS output to Excel. Since the late 1990's, DelGobbo has been working on using SAS/IntrNet and Base SAS software to integrate SAS output with Microsoft Excel and Word, and Gebhart is an expert on ODS tagsets. The techniques presented in this recent paper are the confluence of these technologies.

A key advantage to using the ExcelXP tagset is that it works even if SAS and Excel reside in different locations. You just need to move the SAS output to a location where Excel can access it.

Approach Easily Addresses Customer Needs

DelGobbo's new approach answers a critical need. "Even with all of the complex data systems in use today, programmers and statisticians in the pharmaceutical industry continue to use Excel as the common denominator format in their collaborations with data providers and data consumers," says Jennifer Lester, PharmaSUG 2006 Hands-On Workshop co-chair. "The latest techniques presented here by Vince enable SAS users to do this more efficiently and painlessly than ever before."

In his paper, DelGobbo outlines how to create multi-sheet Excel workbooks that contain your SAS output, without the need to "hand edit" the results in Excel. The paper also explains how to convert your own multi-sheet Excel workbooks to SAS tables. The techniques described in the paper can be used regardless of the operating environment that SAS software is running on, including Windows, UNIX, OpenVMS, and z/OS.

Customers attest that the techniques that DelGobbo has developed work. "Vince's Hands-On-Workshop for us at PharmaSUG was received extremely well," says Brian Shilling, PharmaSUG 2006 Hands-On Workshop co-chair and Manager, Clinical Programming, for BioCor, LLC. in Yardley, PA. "His solutions for importing and exporting SAS data to Excel are very user-friendly, and the examples used by participants were concise and very well presented. Vince has taken the guess work and back-end Excel editing out of this very useful process."

Attend a Workshop or a Talk

DelGobbo has been asked to present this topic at the following meetings:

He will have Hands-On Workshops and talks at the conferences for the Southeast SAS Users Group, South Central SAS Users Group, Northeast SAS Users Group, Midwest SAS Users Group, Pacific Northwest SAS Users Group, and the Western Users of SAS Software in the fall of 2006.

Get The Paper

If you want to read DelGobbo's latest paper or download the sample programs, see support.sas.com/saspresents. Under the heading "SAS Presentations at SUGI 31", scroll to the entry "Creating AND Importing Multi-Sheet Excel Workbooks the Easy Way with SAS."

Any registered, in-house, or local SAS users group can request a presentation for their meeting. If you would like to have this presentation as your annual SAS presentation at an upcoming meeting, please submit a request at least eight weeks before your meeting and use the online User Group Request Form.