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

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

kartheekm9 — Fri, 21 Jun 2019 14:47:54 GMT

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.

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

PaigeMiller — Fri, 21 Jun 2019 14:52:46 GMT

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

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

kartheekm9 — Fri, 21 Jun 2019 15:12:36 GMT

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

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

PaigeMiller — Fri, 21 Jun 2019 15:16:02 GMT

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.

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

Kurt_Bremser — Fri, 21 Jun 2019 15:19:14 GMT

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.

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

kartheekm9 — Fri, 21 Jun 2019 15:20:20 GMT

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

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

ballardw — Fri, 21 Jun 2019 15:20:57 GMT

@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.

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

Kurt_Bremser — Fri, 21 Jun 2019 15:22:10 GMT

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

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

anushreebiotech — Fri, 21 Jun 2019 15:40:52 GMT

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

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

VDD — Fri, 21 Jun 2019 15:51:49 GMT

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.