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;

Example Libname Statement to Connect to a local MySQL Database

Example SAS libname statement to connect to a local MySQL database:
libname mys mysql user=your-username password=your-password database=your-database-name server='127.0.0.1' port=3306;

The above would also work with
server='localhost'

To connect to a non-local database, simply change the IP address or server name of the server. Please note these statements will only work if the SAS/ACCESS interface is installed.

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);

Example Libname Statement to Connect to a Password-Protected Microsoft Access MDB File

Example SAS libname statement to connect to a password-protected MDB file:
LIBNAME myLib "\\filepath\database.mdb" DBPW="your_password";

In my experience, the password must always come after the file path.