A Time-Saving SAS Macro to Facilitate Data Import from Excel

• Activity Number: 246
• Type: Contributed
• Date/Time: Monday, August 2, 2010 : 2:00 PM to 3:50 PM
• Sponsor: Section on Statistical Consulting
• Author(s): Alan C. Elliott*+ and Linda S. Hynan
• Companies: UT Southwestern Medical Center and UT Southwestern Medical Center
• Keywords: Data cleaning ; Data entry ; Data dictionary ; SAS Macro ; Range checking ; Excel

Abstract

In this data-centric world, statisticians in research environments are often faced with the time-consuming task of preparing data sets for analysis. In our medical research environment; data provided by colleagues are most commonly in Excel format. These data sets may contain hundreds of variables. This paper describes a time-saving SAS macro that utilizes information in a data dictionary file to create the SAS program statements needed to define variable names, labels, and formats; assign missing values; and to perform basic data range checks. The SAS program created by the macro can then be used to import data from an Excel file and to create a SAS data set that includes appropriate formats, labels, and missing value assignments. In addition, the program produces a report that indicates out of range values (for either continuous or categorical data) found in the data set.