SAS Date Calculator Now Available

SAS Date Calculator*
Date:

Days since 1/1/1960:

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.

To use the SAS Date Calculator/Converter, enter a date value in the first text box. 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.

The SAS Datetime Calculator works similarly but calculates Datetimes.

To convert dates and datetimes in SAS, use code like this:

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

To convert a date in Excel, subtract 21,916:

To convert a datetime in Excel, subtract 21,916, then multiply by 86,400:

Proc SQL: Using Joins in an Update Statement

I ran into simple use case the other day -- with a surprising outcome. There was a parent and a child table, and I wanted to use an UPDATE query in PROC SQL to update one field of all parent records linked to a child record with a certain value.

In MS Access, the following worked just fine:

UPDATE tblParent INNER JOIN tblChild ON tblParent.pID = tblChild.pID
SET tblParent.field = "updated" WHERE (((tblChild.yesno)=1)) ;

The same statement did not work in SAS. Apparently, PROC SQL does not allow joins in the beginning of an UPDATE statement:
9    PROC SQL;
10   UPDATE tblParent JOIN tblChild ON tblParent.pID = tblChild.pID
                      ----
                      22
                      76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, AS, SET.
ERROR 76-322: Syntax error, statement will be ignored.
11   SET tblParent.field = "updated" WHERE (((tblChild.yesno)=1));
12   QUIT;

In the end, I had to use a subquery. It was probably less efficient than a join, but my table wasn't large enough for it to be a big deal. The solution:
PROC SQL;
   UPDATE tblParent A
   SET field = (SELECT DISTINCT "updated" FROM tblChild B WHERE B.pID = A.pID AND B.yesno=1);
QUIT;

If anyone has a better solution to this or some sort of workaround, please post a comment below! I tried searching Google for a better solution but did not find one.

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.

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;

Need help choosing data visualization colors? Try ColorBrewer2.org

Are you making a data visualization and interested in adding a custom set of colors to make the information really fly off of the page?

If so, I recommend checking out ColorBrewer2.org because it has all sorts of great tools for choosing colors, including color calculators.  It also has tips on choosing color combinations that are color-blind friendly, print friendly, and more.

My favorite SAS proc: proc freq

No doubt, my favorite proc step is proc freq. It is extremely simple, but therein lies its beauty: it's fast to run and easy to understand. With just two lines of code (almost zero effort), it delivers a set of frequency output for all variables in one printout that anyone can understand, regardless of their involvement with the actual data set. I've found raw proc freq output to be a terrific starting point for conversations on data cleaning or data exploration, no matter who is involved in the conversation:

PROC freq data=NAME;
run;

Free SAS Date Calculator

SAS Date Calculator*
Date:

Days since 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.

To convert a date in Excel, subtract 21,916:

Free SAS Datetime Calculator

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.

To convert a datetime in Excel, subtract 21,916 and multiply by 86,400:

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.

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 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/.

Importing Multiple Spreadsheets in SAS for Windows

What if you had to integrate data from a large number of spreadsheets scattered throughout a Windows file directory, including a number of subdirectories? Traversing a directory and collecting file paths might sound like a daunting proposal, but SAS can handle it. This post sketches out a possible solution in three steps.

Step one is to create a master list of all files in the directory, including all subdirectories:

filename DIRLIST pipe 'dir "C:\YOUR\FILE\LOCATION\" /s /b';
data dirlist;
     length buffer $256;
     infile DIRLIST length=reclen;
     input buffer $varying256. reclen;
run;

The above is a variation on SAS's solution to the problem, which is posted at http://support.sas.com/kb/24/820.html. The only difference in the above is that the MS DOS dir command contains /b, which allows the output table to populate with simple list of file paths. Read more about the dir command at http://www.computerhope.com/dirhlp.htm.

Step two is to create a subset of DIRLIST containing only the files to be accessed. If the desired files all have a common string in their file name, this PROC SQL statement will do the job:

PROC SQL;
CREATE TABLE myTable AS
SELECT * FROM DIRLIST WHERE buffer LIKE "%myString%";
QUIT;

If there is no common string in the file names, then your situation is more difficult. Nevertheless, chances are good that your data are all in one type of file, such as .xls, so you can create your subset of file paths based on whatever your extension may be:

PROC SQL;
CREATE TABLE myTable AS
SELECT * FROM DIRLIST WHERE buffer LIKE "%.xls%";
QUIT;

Step three is to loop through each one of the records in myTable, importing data from a single file on each pass. One way to create the outer loop is to adopt and adapt the SCANLOOP macro, which has been published at http://www2.sas.com/proceedings/sugi26/p093-26.pdf. A PROC IMPORT statement or DATA step (with INFILE statement) can be included within the SCANLOOP macro to pull in a file's data on each step - here is a possible example when each spreadsheet that you want to bring in contains two columns:

/* Filename statement to point to file to bring in */
/* LOC is macro variable containing file path */
filename MyImportFile "&LOC";
/* Import data into MyImportFile dataset */
DATA MyImportTEMP ; 
INFILE MyImportFile DLM=',|' MISSOVER DSD linesize=32767;
        /* Include needed number of variables */
LENGTH var1 $ 100 var2 $ 100;
INPUT var1 var2; 
RUN; 
/* Merge MyImportTEMP into MyImportALL dataset */
PROC DATASETS;
APPEND BASE=MyImportALL DATA=MyImportTEMP FORCE;
RUN; 

The above script will work when all of the files have some a common structure. If there is more than one type of file structure, one option would be to use one such PROC IMPORT or DATA step for each type, then use macro variables + IF/THEN logic to make sure that the correct one runs for each of your files.

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;

New feature in SAS 9.3 - LIBNAME statement to connect to JMP dataset

New feature in SAS 9.3:


LIBNAME JMP associates a libref with a JMP data table and enables you to read and write JMP data tables.
http://support.sas.com/documentation/cdl/en/whatsnew/64209/HTML/default/viewer.htm#lestmtsrefwhatsnew93.htm 

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;