How to use today's date in where condition in proc sql?

[kartheekm9]

I'm trying to create a table using proc sql with date(in DATE9. format) as on of the column.

I'm trying to filter the data using where on the date column in the proc sql. I want to have today's date in the condition.

My code looks like:

PROC SQL;
create table work.sheet as
select a, b, date

from library

where date>= '02-01-2018' and date <= today();

quit;

The error message says;

SQL0440N No authorized routine named "TODAY" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

it works well when I use, where date>= '02-01-2018' and date <= '05-31-2019';

Can someone help me on this?

Thank you.

[PaigeMiller]

where date>= '01FEB18'd and date <= "&sysdate"d;

[kartheekm9]

Thank you Paige for the prompt reply. I'm still getting an error though:

SQL0104N An unexpected token "d" was found following "date <="21JUN19"". Expected tokens may include: "<update_source_no_row_query>". SQLSTATE=42601

I removed the d as it was saying, and still got an error:

SQL0206N "21JUN19" is not valid in the context where it is used. SQLSTATE=42703

[PaigeMiller]

The error message as it appears in your last message is meaningless to us unless we can see the code you are using. So ...

Show us a portion of your data.

Show us the full SASLOG, not just the error messages, and please paste the log into the window that appears when you click on the {i} icon; do not show us the SASLOG any other way.

[Kurt_Bremser]

I guess this happens because of the way the implicit pass-through works. What happens if you use today() as the upper bound?

Edit: forget it. You already got a complaint for that in your first post.

[kartheekm9]

No luck with that too,

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "TODAY" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

[Kurt_Bremser]

Please post the complete log of the complete SQL; include any libname definitions you use to connect to DB/2.

[ballardw]

---

@kartheekm9 wrote:

Thank you Paige for the prompt reply. I'm still getting an error though:

SQL0104N An unexpected token "d" was found following "date <="21JUN19"". Expected tokens may include: "<update_source_no_row_query>". SQLSTATE=42601

I removed the d as it was saying, and still got an error:

SQL0206N "21JUN19" is not valid in the context where it is used. SQLSTATE=42703

---

One suspects that you are connecting to an external DBMS and that the message is from the other system, MS SQL Server perhaps?

If you are using Pass through SQL then your syntax needs to conform to the external system.

[anushreebiotech]

Hello,

Please check this:

PROC SQL;
create table work.sheet as
select a, b, date

from library

where date>= '02-01-2018'd and date <= today();

quit;

It worked for me.

Regards,

Anushree

[VDD]

Welcome to the world of dealing with dates. This link may help you in understanding what the hay today() is and how you can use it.

https://v8doc.sas.com/sashtml/lrcon/zenid-63.htm

You do have to put on your thinking cap when working with dates in any programming language.