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

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.