Tuesday, August 30, 2011

SAS Macro to Import Excel Files into SAS

/***********************************************************
IMPORT EXCEL files into SAS
-works with both xls and xlsx files
-assumes all files to be imported are in the same folder

-will pull in the first sheet only; for others, use RANGE option in PROC IMPORT statement
August 30, 2011
http://sastipsbyhal.blogspot.com/
*/
%LET file1 = myFirstFile.XLSorXLSX;
%LET file2 = mySecondFile.
XLSorXLSX;

%LET file3 = myThirdFile.XLSorXLSX;
%LET file4 = myFourthFile.
XLSorXLSX;
%LET file5 = myFifthFile.
XLSorXLSX;
%LET file6 = mySixthFile.
XLSorXLSX;
%LET file7 = mySeventhFile.
XLSorXLSX;
%LET file8 = myEighthFile.
XLSorXLSX;
%LET fileName1 = sasNameFor_myFirstFile;
%LET fileName2 = sasNameFor_mySecondFile;
%LET fileName3 = sasNameFor_myThirdFile;
%LET fileName4 = sasNameFor_myFourthFile;
%LET fileName5 = sasNameFor_myFifthFile;
%LET fileName6 = sasNameFor_MySixthFile;
%LET fileName7 = sasNameFor_mySeventhFile;
%LET fileName8 = sasNameFor_myEighthFile;
%LET fileLoc = \\folder\where\txt\files\are\located\;
%LET fileMax = 8;
%MACRO excelIMPORT;
%Do i = 1 %to &fileMax. ;
PROC IMPORT OUT=WORK.&&fileName&i
            DATAFILE= "&fileLoc&&file&i"
            DBMS=EXCEL REPLACE;
     GETNAMES=YES;
     MIXED=NO;

       SCANTEXT=YES;
       USEDATE=YES;
       SCANTIME=YES;
RUN;
%END;
%MEND;
%excelIMPORT;