02-07-2012 08:47 PM
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
where to_char(enc.end_dt, 'YYYY-MM-DD') between &begencyr. and &endencyr.;
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.
02-07-2012 09:24 PM
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.
02-07-2012 11:31 PM
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?
02-08-2012 09:36 AM
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.
03-20-2012 08:43 AM
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).
02-08-2012 02:38 PM
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.
02-09-2012 03:09 AM
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.