As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


A Macro for Importing Multiple Excel Worksheets into SAS® Data Sets

From sasCommunity
Revision as of 09:40, 14 June 2016 by Paulkaefer (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
Helen Sun and Cindy Wong
Robarts Clinical Trials, London, ON, Canada

Abstract

Microsoft Excel is a widely available application and is commonly used to capture and transmit data. These data are often imported to SAS for manipulation and analysis. Many methods exist to import data from Excel to SAS, such as PROC IMPORT, IMPORT WIZARD and ODBC. In this paper, we introduce a macro, %xl2sas, which uses the powerful Dynamic Data Exchange (DDE) technique[1] to import all the worksheets within an Excel workbook by one invocation. %xl2sas can automatically handle a variable number of worksheets and generate separate SAS datasets without specifying the name of each worksheet as required in the method presented by Qi2. This SAS macro was initially developed as a solution to facilitate data transfers in a clinical trials setting. As Excel files often contain multiple worksheets, we believe %xl2sas is a flexible and user-friendly tool that has wide applicability for the importing of Excel to SAS.

Online resources

You can download the .pdf of this paper here.

Notes

  1. Vyverman, K. "Using Dynamic Data Exchange to Export Your SAS® Data to MS Excel – Against All ODS, Part I". Proceedings of the 26th Annual SAS Users Group International Conference, Paper 011-26, 2001.