Help using Base SAS procedures

Using today() function in a where condition

Reply
Regular Contributor
Posts: 186

Using today() function in a where condition

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

Trusted Advisor
Posts: 1,630

Re: Using today() function in a where condition

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

Super User
Super User
Posts: 6,502

Re: Using today() function in a where condition

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 ;

Valued Guide
Posts: 2,175

Re: Using today() function in a where condition

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.



Trusted Advisor
Posts: 1,630

Re: Using today() function in a where condition

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.

Valued Guide
Posts: 2,175

Re: Using today() function in a where condition

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

Ask a Question
Discussion stats
  • 5 replies
  • 259 views
  • 0 likes
  • 4 in conversation