topic Using today() function in a where condition in SAS Procedures

Using today() function in a where condition

nicnad — Mon, 04 Nov 2013 16:48:29 GMT

Hi, I have the following SQL Code.

%let mydatepres = today() ;

PROC SQL;

CREATE TABLE test AS SELECT histo.*

FROM ICOPLIB.histo AS histo

WHERE histo.reportproductiondate = &mydatepres

;QUIT;

I'm having issue with the formatting of my variable in order to use it properly.

What is the proper way to put today() function value in a variable and use it in a where statement?

My value histo.reportproduction is formatted datetime20.0

Thank you for your help and time

Re: Using today() function in a where condition

PaigeMiller — Mon, 04 Nov 2013 16:55:29 GMT

%let mydatepres = %sysfunc(today(),date9.) ;

PROC SQL;

CREATE TABLE test AS SELECT histo.*

FROM ICOPLIB.histo AS histo

WHERE datepart(histo.reportproductiondate) = "&mydatepres"d

;QUIT;

Re: Using today() function in a where condition

Tom — Mon, 04 Nov 2013 16:58:22 GMT

That would work if ICOPLIB is pointing to a SAS library.

If it is pointing to a database server using SAS/ACCESS then I do not think that SAS can pass the TODAY() function call into the database. Instead try converting the TODAY() function result into a date literal.

%let mydatepres = "%sysfunc(today(),date9.)"d ;

Re: Using today() function in a where condition

Peter_C — Mon, 04 Nov 2013 19:20:49 GMT

and if the database presents the value as a timestamp, as @PaigeMiller suggests you could use the datetime() function instead of the today()

WHERE histo.reportproductiondate = "%sysfunc( datetime(), datetime)"dt

that might be too accurate, so just try

WHERE histo.reportproductiondate = "%sysfunc( date(), date):00:00:00"dt

I think it is likely that performing a datepart() function on a database variable would perform less well than a function on a constant in the sas macro language environment.

Re: Using today() function in a where condition

PaigeMiller — Tue, 05 Nov 2013 19:29:05 GMT

I think it is likely that performing a datepart() function on a database variable  would perform less well than a function on a constant in the sas macro language environment.

I don't understand this.

WHERE  histo.reportproductiondate = "%sysfunc( date(), date):00:00:00"dt

But this only matches histo.reportproductiondate that is exactly midnight (:00:00:00), and I'm guessing that the original request was to match any record in the database that had today's date.

Re: Using today() function in a where condition

Peter_C — Tue, 05 Nov 2013 19:46:58 GMT

To avoid applying a SAS function to the database table use the sql range operator BETWEEN

WHERE histo.reportproductiondate BETWEEN "%sysfunc( date(), date):00:00:00"dt AND "%sysfunc( date(), date):23:59:59"dt

This ensures easy implementation of the WHERE clause inside the databse, to avoid having the whole table transferred to SAS to implement a SAS function