Hi,
I am trying to run a code like below:
LIBNAME eee META LIBRARY=zz_Oracle_zz_Library;
%Let begencyr = '2010-01-01';
%Let begrptyr = '01Jan2011'D;
PROC SQL outobs=10;
CREATE TABLE TEMPTEST AS
SELECT inpat_end_dt
from
eee.rerere enc;
where to_char(enc.end_dt, 'YYYY-MM-DD') between &begencyr. and &endencyr.;
RUN;
QUIT;
I am getting the following warning:
20 where to_char(enc.end_dt, 'YYYY-MM-DD') between &begencyr. and &endencyr.;
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.
Please let me know what is wrong.Is to_char not supported in EG?
Thanks in advance.
RR
TO_CHAR is an Oracle-specific SQL function. Your code uses SAS SQL.
Suggest you change your WHERE statement to: where enc.end_dt between &begencyr. and &endencyr.;
This assumes of course that END_DT is defined in Oracle as data type DATE. You may want to confirm this.
Thanks.End_dt field is defined as date time format.Is there any way I can convert the end_dt variable in the above format in the WHERE clause itself?
Also,the above code runs in PC sas ,so I thought it would run in SAS EG...any thoughts?
Renjithr,
On "Also,the above code runs in PC sas ,so I thought it would run in SAS EG...any thoughts?", the reason that it runs directly through your SAS server and not when it is coming from EGuide is likely because EGuide has set some system option that prevents the passthrough like that. To debug that sort of issue is a little tedious. My way is to open EGuide, turn logging on, close EGuide, open EGuide (this gets some of those "hidden" options to surface), run your program, and then look at the log. Once you find the offending option, you may be able to turn it back off in your code. If Chris sees this, he may have an easier way to find the problem.
Doc Muhlbaier
Duke
It looks like others have already chimed in on the syntax of this particular example, but I'll just add that one difference in EG is that EG sets OPTIONS VALIDVANAME=ANY by default. That can sometimes have an effect, though usually it affects only operations such as PROC IMPORT (transcribing Excel column names in a different way).
Chris
Since the end_dt column is date time then this WHERE should work:
where enc.end_dt between '01JAN2012:00:00:00'dt and '31JAN2012:00:00:00'dt;
The key thing here is the use of DT on the datetime string to tell SAS this is a datetime and to handle it accordingly. SAS should then parse this correctly into PL/SQL when run on an Oracle database.
Apart from the date formatting issue, you have a ; after the from clause, so there is no chance that this code run without error in any SAS session.And that is what causes the error.
And I don't think that this is valid in Oracle SQL either.
There is also a RUN, which also is not valid SQL syntax, but will only render in a NOTE.
/Linus
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.