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.
where date>= '01FEB18'd and date <= "&sysdate"d;
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
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.
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.
Please post the complete log of the complete SQL; include any libname definitions you use to connect to DB/2.
@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.
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.