BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

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

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Tom
Super User Tom
Super User

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
Rhodochrosite | Level 12

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
Diamond | Level 26
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.

--
Paige Miller
Peter_C
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2327 views
  • 0 likes
  • 4 in conversation