Bring Excel file with multiple sheets to SAS

NESUG 2012 Coders' Corner Bring Excel file with multiple sheets to SAS® Mindy Wang, Marriott International, Bethesda, MD ABSTRACT In today’s work place, Excel files seem to be the most common files that we deal with. Sometimes we encounter data come in as an excel file with multiple spread sheets. This paper illustrates three approaches as to bring the spread sheets into SAS, where each sheet becomes a SAS dataset. This first method is using DDE (Dynamic Data Exchange). The second method is using a simple macro program to import the multiple spreadsheets one by one. This third method is setting up the excel file as a SAS library and bring in each sheet as a member in the library. The last method is definitely very convenient and easy to use when there are many sheets involved. INTRODUCTION This paper illustrates two methods to bring in excel file with multiple spreadsheets. Following is the original Excel file. 1 NESUG 2012 Coders' Corner DDE METHOD The first method is using DDE (Dynamic Data Exchange). For DDE to work, first we need to have the Excel spreadsheet open, when we run the SAS program. Also we need to specify the specific variables and data types (such as numeric or character), as well as the ranges of rows and columns which contain data in the SAS program. Then when we bring the next sheet to SAS, we need to open the next sheet in Excel and type in all the variable information in the SAS program. While this method is working, if the Excel file contains many spreadsheets, 2 NESUG 2012 Coders' Corner this method can be very tedious. We need to manually open each sheet and for the very minimum, we need to know all the variable names and data types (numeric or character) in each sheet. There is still room for improvement. Following is the program code using DDE. filename in1 DDE "excel|E:\NESUG2012\[example2.xls]DM!r1c1:r300c1000"; data DM; infile in1 dsd dlm='09'x notab missover firstobs=2; input INVESTIGATOR PATIENT SEX RACE $ HEIGHT_CM WEIGHT_KG age TREATMENT; run; filename in2 DDE "excel|desktop\[example.xls]MH!r1c1:r300c1000"; data MH; infile in2 dsd dlm='09'x notab missover firstobs=2; input INVESTIGATOR PATIENT BODY_SYSTEM ABNORMALITY $ TREATMENT; run; .............. USE MACRO TO EXPORT THE FILE We can write a simple macro program to bring in the multiple sheets one by one to SAS using the following SAS codes. This program has the advantage of not needing to open the Excel spread sheet one by one as the DDE methods mentioned above. Also, we don’t need to type in the variable names and types. However in this program, we still need to put in the sheet name one by one. There is still room for improvement. options mprint; %macro imp (insheet=); proc import out=work.&insheet datafile= "E:\NESUG2012\example2.xls" dbms=excel replace; sheet="&insheet.$"; getnames=YES; mixed=NO; scantext=YES; usedate=YES; scantime=YES; run; %mend imp; %imp(insheet=DM) %imp(insheet=MH) %imp(insheet=VS) USE LIBNAME TO POINT TO THE EXCEL FILE In SAS® version 9.1 or up, there is an option to treat Excel file as a library and all the sheets in the excel file as data sets in the library. With this improvement, it is much easier to bring the multiple spreadsheets to SAS. If you don’t mind that the data sets coming from Excel end with $ at the end of the data set names, then you are done by setting up the libname. Each excel spreadsheet is now a SAS data set in the library. In this example, they are DM$, MH$, and VS$. Following is the screenshot of the library. 3 NESUG 2012 Coders' Corner However, if you need to have a dataset name without the $ sign at the end, there are still some work need to be done. With the libname method, we can use proc sql to query dictionary.tables to find out how many sheets the Excel file has and save it as a macro variable. We can also save the sheet names as a macro variable. Then with a simple macro program all the import procedures can be done to multiple sheets. Macro programs m1, m2, and m3 are three alternatives the datasets without $ sign at the end can be done. Following are the code that brings the spread sheets to SAS: libname myxls excel "E:\NESUG2012\example2.xls"; proc sql; create table a as select * from dictionary.tables where libname="MYXLS" ; quit; proc sql; select memname into :snamlist separated by '*' from a ; quit; proc sql; select count(memname) into :n from a ; quit; 4 NESUG 2012 Coders' Corner %put &snamlist; %put &n; options mprint; %macro m1; %do i=1 %to &n; %let var=%scan(&snamlist,&i,*); PROC IMPORT OUT= WORK.%substr(&var,1,%length(&var)-1) DATAFILE= "E:\NESUG2012\example2.xls" DBMS=EXCEL REPLACE; RANGE="&var"; GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; %end; %mend m1; %m1 /*ALTERNATIVELY USE MACRO PROGRAM m2 INSTEAD OF m1*/ %macro m2; %do i=1 %to &n; %let var=%scan(&snamlist,&i,*); %let sf=%substr(&var,1,%length(&var)-1); data &sf; set myxls."&var"n; run; %end; %mend m2; %m2 /*ALTERNATIVELY USE MACRO PROGRAM m3 INSTEAD OF m1*/ %macro m3; %do i=1 %to &n; %let var=%scan(&snamlist,&i,*); %let sf=%substr(&var,1,%length(&var)-1); proc sql; create table &sf as select * from myxls."&var"n; quit; %end; %mend m3; %m3 Following is the screenshot of the datasets without $ at the end of data set name. 5 NESUG 2012 Coders' Corner CONCLUSIONS Definitely the last method is much easier to apply to other projects as needed. It doesn’t matter how many spreadsheets there are and we don’t need to know what variable names or data types are in each spreadsheet and we don’t need to know what the row or column ranges contain data. This method can be adapted to different project easier. With the proc sql, we pull out the total number of sheets and the sheet names in the excel file and save them as macro variables. Once the macro is set up, the only change we need to do to make it work in another project is to change the libname statement and the file name in the DATAFILE= to point to the right excel file. Much of the typing is largely reduced and less likely errors might occur due to manual manipulation. REFERENCES: ® SAS Macro Language 1: Essentials Course Notes ® Carpenter, Art (2004) Carpenter’s Guide to SAS Macro Language Second Edition ® Burlew, Michele M. (2006) SAS Macro Programming Made Easy Shostak, Jack (2005) SAS® Programming in the Pharmaceutical Industry 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. CONTACT INFORMATION Your comments and questions are valued and encouraged. Contact the author at: Mindy Wang Marriott International 14412 Stonebridge View Dr. North Potomac, MD 20878 6 NESUG 2012 Coders' Corner Work Phone: (240) 855-3479 Email: [email protected] 7