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;



Thursday, July 21, 2011

Wednesday, July 6, 2011

How to create a SAS date from "YYYY-MM-DD HH:MM:SS"

Problem
How to pull a SAS date from a text field formatted as YYYY-MM-DD HH:MM:SS.

Solution
data myTable;
set myTable;
informat dateVar date9.;
format dateVar date9.;
dateVar = input(substr(temp_dt,1,10), yymmdd10.);
run;

Friday, June 10, 2011

SAS Macro to Import Tab-Delimited Files into SAS


/***********************************************************
IMPORT TAB-DELIMITED text files into SAS
June 10, 2011
http://sastipsbyhal.blogspot.com/
*/
%LET file1 = myFirstFile.txt;
%LET file2 = mySecondFile.txt;
%LET file3 = myThirdFile.txt;
%LET file4 = myFourthFile.txt;
%LET file5 = myFifthFile.txt;
%LET file6 = mySixthFile.txt;
%LET file7 = mySeventhFile.txt;
%LET file8 = myEighthFile.txt;
%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 tabdIMPORT;
%Do i = 1 %to &fileMax. ;
PROC IMPORT OUT=WORK.&&fileName&i 
            DATAFILE= "&fileLoc&&file&i"
            DBMS=TAB REPLACE;
    GETNAMES=YES;
    DATAROW=2; 
RUN;
%END;
%MEND;
%tabdIMPORT;