SAS Tips by Hal
A SAS user's site featuring a free SAS date calculator, a free SAS datetime calculator (javascript), code snippets, and brief discussions
Tuesday, June 17, 2014
Friday, September 28, 2012
SAS Date Calculator Now Available
We have added an online, interactive SAS Date Calculator and Converter. It is available for free in this post (above). 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). 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;
Labels:
Dates,
Datetimes,
SAS Date Calculator
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 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;
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;
Or identify records that contain any of the above, any month name, or any month three-letter abbreviation:
where prxmatch('*\d\/\d*', fieldname) >= 1OR 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;
Labels:
Data cleaning,
SAS Macro
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,
/* 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;
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;
Labels:
Importing Files,
SAS Macro