Friday, September 28, 2012

SAS Date Calculator Now Available

Interactive SAS Date Calculator*
Days since 1/1/1960:

Interactive SAS Datetime Calculator*
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 for free in this post (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 for free 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*/
 d = INPUT('21DEC11'd, best12.);
 PUT d;

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

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

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

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

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

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

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;

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;

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='' port=3306;

The above would also work with

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.