Desktop productivity for business analysts and programmers

Oracle-SAS EG code

Reply
Frequent Contributor
Posts: 122

Oracle-SAS EG code

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

Super User
Posts: 3,233

Oracle-SAS EG code

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.

Frequent Contributor
Posts: 122

Re: Oracle-SAS EG code

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?

Trusted Advisor
Posts: 2,114

Oracle-SAS EG code

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

Community Manager
Posts: 2,888

Oracle-SAS EG code

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

Super User
Posts: 3,233

Oracle-SAS EG code

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.

Super User
Posts: 5,383

Oracle-SAS EG code

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

Data never sleeps
SAS Employee
Posts: 1

Oracle-SAS EG code

Hi renjithr ,

The problem for sure is the semi-colon before your where clause Smiley Happy

Cheers,

Gozzi78

Ask a Question
Discussion stats
  • 7 replies
  • 873 views
  • 0 likes
  • 6 in conversation