SAS: converting a datetime to date in where clause

Question

I have a column in a sas dataset that is of datetime 25.6 format; lets call this column datetime. I want to convert it to Date9 format in a where clause and check against a certain date or date variable.

I currently have the following code:

proc sql;
Select rowid, name, dob, country
from db.testTable
where cast(datetime as date9.) eq '14sep2014'd
;
quit;

I get an error when i run the above code:

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

I get the same error message if use the following

proc sql;
Select rowid, name, dob, country
from db.testTable
where cast(datetime as date9.) = '14sep2014'd
;
quit;

Is there a better way to cast a datetime to date9 format in SAS? Any help on this will be greatly appreciated

Answer 1

In SAS you would use the datepart() function to extract the date value from the datetime value:

where datepart(datetime) = '14sep2014'd

There is no need to specify a format, as it does not affect the underlying value.

Answer 2

A second option, that undoubtedly is slightly inferior to datepart unless you have a really large dataset (in which it is a bit faster probably as you have a (hopefully) constant on one side of the expression rather than a function call for each iteration), is to use DHMS to make a datetime:

data _null_;
  x=dhms('01JAN2010'd,0,0,0);
  put x=;
run;

or even use a datetime constant explicitly:

data _null_;
  x='01JAN2010:00:00:00'dt;
  put x=;
run;

(I would expect them to be of identical timings, as SAS should optimize the first to the second, since it's a constant expression - but who knows).

And on a side note, SAS has two primitive types: numeric and character. input turns character to numeric, and put turns numeric to character - the equivalent of cast. Anything else in SAS (e.g., dates) are simply formats applied to numbers (or characters) and have their own special functions (like datepart here).