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;