Friday, September 28, 2012

SAS Date Calculator Now Available

Interactive SAS Date Calculator*
Date:

Days since 1/1/1960:


Interactive SAS Datetime Calculator*
Datetime:

Seconds since midnight 1/1/1960:


*For valid results, SAS date value must be between 1582 CE and 20,000 CE on the Gregorian Calendar. Note that American Colonies and Great Britain did not adopt the Gregorian Calendar until 1752.


We have added an online, interactive SAS Date Calculator and Converter. It is available in this post as a preview (above) and on the right-hand margin of this page. To use it, enter a date value in the first text box, and the unformatted date value (i.e., the number of days since 1/1/1960) that SAS associates with that number will appear in the second text box. The calculator also works in reverse.

We have also added a SAS Datetime Calculator and Converter, also available in this post (above) and on the right-hand margin of this page. It works in the same way as the date calculator, except it calculates Datetimes.

It is also possible to run such conversions within SAS. Example code:

/*print date formatted as number*/
DATA _NULL_;
 d = INPUT('21DEC11'd, best12.);
 PUT d;
RUN;

/*print number formatted as date*/
DATA _NULL_;
 d = 18982;
 FORMAT d date9.;
 PUT d;
RUN;

/*print datetime formatted as number*/
DATA _NULL_;
 dt = INPUT('21DEC11 12:00:35'dt, best12.);
 PUT dt;
RUN;

/*print number formatted as datetime*/
DATA _NULL_;
 dt = 1640088035;
 FORMAT dt datetime.;
 PUT dt;
RUN;

Friday, July 6, 2012

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;

Wednesday, June 13, 2012

SAS Macro to Import all Worksheets of an XLSX file

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

/* 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;

Two Methods to Create a CSV: Proc Export and the Data Step

Sample PROC Export procedure to export a SAS dataset to CSV:

proc export data=work.DATASET_NAME;
     outfile='c:\users\public\EXPORTED_FILE_NAME.csv'
     replace
     dbms=dlm;
     delimiter=',';
run;

Sample DATA Step to export a SAS dataset to CSV:

data _null_;
     set work.DATASET_NAME;
     file 'c:\users\public\EXPORTED_FILE_NAME.csv' dlm=',';
     put var1 var2 var3;
run;


The above two statements are essentially equivalent, with two exceptions: the data step does not output field names to the CSV file, and the data step requires that desired variables be explicitly included in the PUT statement.

Thursday, May 17, 2012

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.

Tuesday, March 13, 2012

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.

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

Tuesday, January 31, 2012

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;

Friday, December 9, 2011

SAS User who needs to use R?

Are you a SAS user who needs to get something done in R? Instead of learning R, consider using some of the SAS <-> R resources available on the web.

The following website seems to have terrific resources for switching back and forth between the two: http://sas-and-r.blogspot.com/. Stack Overflow is probably the web's best resource for computer programming and configuration: http://stackoverflow.com/.