SAS Macro to Print Record Counts for All Tables in a Library

/*----- PRINT RECORD COUNTS OF ALL TABLES IN LIBRARY -----*/
/* Looping code from Paper 93-26 by Edward Moore:
http://www2.sas.com/proceedings/sugi26/p093-26.pdf
Many thanks to the author. */
%MACRO countRecs(lib, tempListTable, tempOutputTable);
/*----- Make list of all tables in library -----*/
PROC SQL noprint;
CREATE TABLE &tempListTable AS
SELECT DISTINCT memname
FROM dictionary.tables
WHERE UPCASE(LIBNAME) = UPCASE("&lib");
QUIT;


/*----- Scan through &tempListTable -----*/
DATA _NULL_;
IF 0 THEN SET &tempListTable NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;


/*----- Make table to hold record counts -----*/
DATA &tempOutputTable;
LENGTH tbl $29. ct 8;
RUN;


/*----- Loop -----*/
%DO I=1 %TO &RECCOUNT;
/* Advance to the Ith record */
DATA _NULL_;
SET &tempListTable (FIRSTOBS=&I);
CALL SYMPUT('tbl',memname);
STOP;
RUN;
/* Populate datasets */
PROC SQL noprint;
INSERT INTO &tempOutputTable (tbl, ct) SELECT "&tbl" as tbl, COUNT(*) as ct FROM &lib..&tbl;
QUIT;
%END;


/*----- Print Result -----*/
PROC PRINT DATA = &tempOutputTable;
RUN;


/*----- Delete Temporary Tables -----*/
PROC DATASETS LIBRARY=work noprint;
DELETE &tempListTable &tempOutputTable;
QUIT;
%MEND countRecs;


%countRecs(WORK, kljgKGJ3208tjg, asvbjik4o9ksjd9);

SAS Macro to Import CSV Files into SAS

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

Sample "Where" Clauses for Finding Dates in Text Fields

The following where clauses use SAS PRXMatch functions and simple Perl regular expressions to help find dates within SAS dataset text fields.

Identify records that contain number/number, e.g. 1/2, 12/25, etc.:

where prxmatch('*\d\/\d*', fieldname) >= 1;

Identify records that contain number-number, e.g. 1-2, 12-25, etc.:

where prxmatch('*\d-\d*', fieldname) >= 1;

Identify records that contain four-digit numbers, e.g. 2007:

where prxmatch('*\d\d\d\d*', fieldname) >= 1;

Identify records that contain the word "July":

where prxmatch('*July*', fieldname) >= 1;

Or identify records that contain any of the above, any month name, or any month three-letter abbreviation:

where prxmatch('*\d\/\d*', fieldname) >= 1
OR prxmatch('*\d-\d*', fieldname) >= 1
OR prxmatch('*\d\d\d\d*', fieldname) >= 1
OR prxmatch('*January*', fieldname) >= 1
OR prxmatch('*Feburary*', fieldname) >= 1
OR prxmatch('*March*', fieldname) >= 1
OR prxmatch('*April*', fieldname) >= 1
OR prxmatch('*May*', fieldname) >= 1
OR prxmatch('*June*', fieldname) >= 1
OR prxmatch('*July*', fieldname) >= 1
OR prxmatch('*August*', fieldname) >= 1
OR prxmatch('*September*', fieldname) >= 1
OR prxmatch('*October*', fieldname) >= 1
OR prxmatch('*November*', fieldname) >= 1
OR prxmatch('*December*', fieldname) >= 1
OR prxmatch('*Jan*', fieldname) >= 1
OR prxmatch('*Feb*', fieldname) >= 1
OR prxmatch('*Mar*', fieldname) >= 1
OR prxmatch('*Apr*', fieldname) >= 1
OR prxmatch('*May*', fieldname) >= 1
OR prxmatch('*Jun*', fieldname) >= 1
OR prxmatch('*Jul*', fieldname) >= 1
OR prxmatch('*Aug*', fieldname) >= 1
OR prxmatch('*Sep*', fieldname) >= 1
OR prxmatch('*Oct*', fieldname) >= 1
OR prxmatch('*Nov*', fieldname) >= 1
OR prxmatch('*Dec*', fieldname) >= 1;

Or, a macro that will print any records that match the above:

%macro dsearch(tablename, fieldname);
    proc print data = &tablename;
        var &fieldname;
        where prxmatch('*\d\/\d*', &fieldname) >= 1
            OR prxmatch('*\d-\d*', &fieldname) >= 1
            OR prxmatch('*\d\d\d\d*', &fieldname) >= 1
            OR prxmatch('*January*', &fieldname) >= 1
            OR prxmatch('*Feburary*', &fieldname) >= 1
            OR prxmatch('*March*', &fieldname) >= 1
            OR prxmatch('*April*', &fieldname) >= 1
            OR prxmatch('*May*', &fieldname) >= 1
            OR prxmatch('*June*', &fieldname) >= 1
            OR prxmatch('*July*', &fieldname) >= 1
            OR prxmatch('*August*', &fieldname) >= 1
            OR prxmatch('*September*', &fieldname) >= 1
            OR prxmatch('*October*', &fieldname) >= 1
            OR prxmatch('*November*', &fieldname) >= 1
            OR prxmatch('*December*', &fieldname) >= 1
            OR prxmatch('*Jan*', &fieldname) >= 1
            OR prxmatch('*Feb*', &fieldname) >= 1
            OR prxmatch('*Mar*', &fieldname) >= 1
            OR prxmatch('*Apr*', &fieldname) >= 1
            OR prxmatch('*May*', &fieldname) >= 1
            OR prxmatch('*Jun*', &fieldname) >= 1
            OR prxmatch('*Jul*', &fieldname) >= 1
            OR prxmatch('*Aug*', &fieldname) >= 1
            OR prxmatch('*Sep*', &fieldname) >= 1
            OR prxmatch('*Oct*', &fieldname) >= 1
            OR prxmatch('*Nov*', &fieldname) >= 1
            OR prxmatch('*Dec*', &fieldname) >= 1
    ;
    run;
%mend dsearch;

SAS Macro to Import all Worksheets of an XLSX file

/* SAS Macro to import all worksheets of an XLSX file */

/* http://sastipsbyhal.blogspot.com/ */

/* Extra required software includes SAS/ACCESS, SAS/MACRO */
/* Assumes first row contains field names */

/* Assumes worksheets have valid SAS dataset names */ 

/* This macro provided as-is, use at your own risk */

/* Macro parameters:
    libin = libname to be temporarily assigned to XSLX file
    libinpath = path to XLSX file
    libout = libname where SAS datasets should be exported
*/

%macro XLSXimport(libin, libinpath, libout);
    /* Open link to xlsx file */
    libname &libin. "&libinpath";

    proc sql noprint;
        /* Create array with name of each dataset */
        select memname into :mem1 - :mem&sysmaxlong
        from dictionary.tables
        where libname=upcase("&libin") 

        and memtype = upcase('data');

        /* Loop to import each dataset to libout */
        %do i=1 %to &sqlobs;
            create table &libout..%substr(&&mem&i,1,

                %EVAL(%LENGTH(&&mem&i)-1)) as
            select * from &libin.."&&mem&i"n ;
        %end;
    quit;

    /* Close link to xlsx file */
    libname &libin. CLEAR;
%mend XLSXimport;

%XLSXimport(mylib, c:\users\public\EXCEL FILE.xlsx, work);
run;

SAS Macro to Export All Datasets in Library as SPSS Files

/*********************************************
Export all datasets in library as SPSS
Looping code from Paper 93-26 by Edward Moore:
http://www2.sas.com/proceedings/sugi26/p093-26.pdf
Many thanks to the author.
*********************************************/
%MACRO exportSPSS(filepath, library);
/*----- Create Temporary Table -----*/
PROC SQL;
CREATE TABLE TempTable_sakg9389j AS
SELECT DISTINCT memname
FROM DICTIONARY.TABLES
WHERE LIBNAME="&library";
QUIT;


/*----- Scan through &tempListTable -----*/
DATA _NULL_;
IF 0 THEN SET TempTable_sakg9389j NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;


/*----- Loop -----*/
%DO I=1 %TO &RECCOUNT;
/* Advance to the Ith record */
DATA _NULL_;
SET TempTable_sakg9389j (FIRSTOBS=&I);
CALL SYMPUT('tbl',COMPRESS(memname));
STOP;
RUN;
/* Export */
PROC EXPORT DATA= &library.&tbl 
            OUTFILE= "&filepath.\&tbl..sav"
            DBMS=SPSS REPLACE;
RUN;
%END;


/*----- Delete Temporary Table -----*/
PROC DATASETS noprint;
DELETE TempTable_sakg9389j;
QUIT;
%MEND exportSPSS;


%exportSPSS(C:\Users\USER_NAME\Documents, WORK);

PROC SQL: Select Values into Macro Variables

In SAS, it is possible to select/save values into macro variables within PROC SQL. Here are some examples:

One Variable (Summarized)
PROC SQL;
    SELECT SUM(field1)
    INTO :var1
    FROM table;
QUIT;


Multiple Variables (Summarized)
PROC SQL;
    SELECT SUM(field1), COUNT(field2)
    INTO :var1, :var2
    FROM table;
QUIT;


One Variable (Multiple values saved to an array)
PROC SQL;
    SELECT field1
    INTO :var1 - :var9999
    FROM table;
QUIT;


Multiple Variables (Multiple values saved to multiple arrays)
PROC SQL;
    SELECT field1, field2
    INTO :var1 - :var9999, :x1 - :x9999
    FROM table;
QUIT;

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;

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;