NESUG 2011 Coders' Corner ABSTRACT

1 1 READING MULTIPLE SHEETS FROM EXCEL USING SAS Sandeep Kottam, Programmer consultant, Independent Consultant, NJ. Sree Lakshmi K Tripuraneni, Programmer consultant, Purdue Pharma, Stamford, CT. ABSTRACT : When importing data from Microsoft Excel into SAS you can use the IMPORT Wizard, PROC IMPORT, ODBC, PROC DBLOAD, PROC ACCESS, DDE (Dynamic Data Exchange). When multiple worksheets need to be imported, it adds more complexity. We have to specify the different sheet names by hard coding, so that the sheet names are visible to the SAS system. In most of the multi-sheet situations, the number of worksheets, sheet names happens to be different, and sometimes the column names in sheets are too messy to be used. This paper describes how to read in multiple sheets from a work book, append the datasets created by importing sheets.

2 This paper shows how it can be done using SAS and SAS (different codes). INTRODUCTION: In the pharmaceutical industry, External information from vendors is usually stored in Excel spreadsheets. Normally for Same studies, the sheet names and Column names are different when there are spaces, thus using hard coding is very time consuming and error prone. An automated SAS macro would be a desirable utility tool to accomplish this task. This paper introduces a macro which reads Excel files that have multi-sheets using a recursive macro technique and the Excel LIBNAME engine. The Excel LIBNAME engine was introduced in SAS version 9 and constructed. By using this new LIBNAME engine, along with other data transfer tools such as PROC EXPORT, PROC IMPORT, PROC SQL or DATA step, transferring data between Excel and SAS can be greatly simplified.

3 Figure 1: Sample Multi-Sheet Excel Work Book Coders' CornerNESUG 2011 2 Figure 1 is an Example for the Multi-Sheet Work Book, with variables STUDY ID SUBJECT ID DRUGNAME START DATE, There are three Sheets with names Source1 Source2 Source3, Each of the Sheet have above mentioned variables, But some times, there may be extra spaces for example Source1 can have STUDY ID and Source2 can have STUDY ID, which may cause some need of hard coding while we want to append the necessary Sheets. To Avoid this Intervention of hard coding the below macro was used. MACRO CODE: %macro xlread; /**Assign a libname for excel sheet*/ LIBNAME XLSLIB "C:\ NESUG -2011\ " access=readonly; /**PART1 OF THE PROGRAM USE PROC SQL TO READ THE SHEET NAMES IN THE EXCEL SHEET SPECIFIED BY THE LIBNAME STATMENT ABOVE**/ /**creating macro variables for the sheet**/ proc sql noprint; /**Get total Number of Sheets**/ select count(distinct(MEMNAME)) into: tot from where LIBNAME ='XLSLIB' AND INDEX(MEMNAME,'General')=0; /**Get the sheet names without $ in to macro variables**/ select distinct(compress(MEMNAME,"',$")) into: s1 - :s%trim(%left(&tot)) from where LIBNAME ='XLSLIB' AND INDEX (MEMNAME,'General')=0.

4 /**Get the sheet names with $ in to macro variables**/ select distinct(MEMNAME) into: v1 - :v%trim (%left(&tot)) from where LIBNAME ='XLSLIB' AND INDEX (MEMNAME,'General')=0; /**GET NAMES WITH OUT THE DELIMETERS**/ select distinct(compress(MEMNAME,"',$,-")) into: c1 - :c%trim(%left(&tot)) from where LIBNAME ='XLSLIB' AND INDEX(MEMNAME,'General')=0; Quit; Coders' CornerNESUG 2011 3 /**PART2 THE ABOVE MACRO VARIABLE TOT IS USE TO RUN A DO LOOP SO THAT WE HAVE TO CREATE NAMES OF THE VARIABLES**/ %do i=1 %to /*Create macro variables to store names of variables for reach dataset*/ proc sql noprint; select COUNT(distinct(NAME)) into: T from where LIBNAME ='XLSLIB' AND MEMNAME="&&v&i.

5 " and SUBSTR(NAME,1,1)^='F'; select distinct(NAME) into: O1 - :O%trim(%left(&T)) from where LIBNAME ='XLSLIB' AND MEMNAME="&&v&i." and SUBSTR(NAME,1,1)^='F'; QUIT; /**set the datasets**/ data set xlslib." RUN; /**renaming the variable names to set all of them**/ PROC DATASETS LIBRARY=WORK; MODIFY %do j=1 %to &T; rename &&O&j. =%sysfunc(compress("&&O&j.","',$,-,_ ")); %end; quit; run; %end; /**SAS Code**/ data final; set data1 - data&tot.: run; /**THIS MACRO WAS USED FROM WEBSITE **/ /**Code from **/ %macro names(prefix,maxnum); %do i=1 %to &prefix&i %end;; %mend names; Coders' CornerNESUG 2011 4 /**SAS Code**/ data final; set %names(Source, run; %mend; %xlread; MACRO OVERVIEW: The macro has the following steps.)

6 1.) The macro imports the Excel sheet using Libname Statement. 2.) Proc SQL will be used to create the names of Sheets in to macro variables with/without $ Symbol which can be used for the loops. 3.) The do loop is used to process all the datasets at a time create variables names for each dataset 4.) Another do loop used to rename the variable names using Proc datasets. 5.) Depending on the SAS Version there are two codes that can be used. CONCLUSIONS: The above method, this compare macro is designed to make your life easier. You can use this code to use it over and over again. We can also Automate the process by passing excel file name and path or Use PIPE to read Excel Sheets in the particular Directory. REFERENCES: Art Carpenter s Carpenter's Complete Guide to the SAS Macro Language , 2ndEdition.

7 Marie Byrd Alexander, Sharon Avrunin-Becker s The Mighty Macro That Will Change How You Macro , NESUG -2008. ACKNOWLEDGMENTS: SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. indicates USA registration. Other brand and product names are registered trademarks or trademarks of their respective companies. Coders' CornerNESUG 2011 5 CONTACT INFORMATION: Your comments and questions are valued and encouraged. Contact the authors at: Sandeep Kottam Programming consultant, 605-691-3274 Sree Lakhsmi K Tripuraneni programmer, Purdue Pharma, Stamford, CT-06905 605-691-3312 Coders' CornerNESUG 2011