Using today() function in a where condition

[nicnad]

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

[PaigeMiller]

%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;

[Tom]

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 ;

[Peter_C]

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.

[PaigeMiller]

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.

[Peter_C]

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