topic Oracle-SAS EG code in SAS Enterprise Guide

Oracle-SAS EG code

renjithr — Wed, 08 Feb 2012 01:47:22 GMT

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.

Oracle-SAS EG code

SASKiwi — Wed, 08 Feb 2012 02:24:51 GMT

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.

Re: Oracle-SAS EG code

renjithr — Wed, 08 Feb 2012 04:31:25 GMT

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?

Oracle-SAS EG code

Doc_Duke — Wed, 08 Feb 2012 14:36:07 GMT

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

Oracle-SAS EG code

SASKiwi — Wed, 08 Feb 2012 19:38:10 GMT

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.

Oracle-SAS EG code

LinusH — Thu, 09 Feb 2012 08:09:39 GMT

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

Oracle-SAS EG code

Gozzi78 — Fri, 16 Mar 2012 18:51:50 GMT

Hi renjithr ,

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

Cheers,

Gozzi78

Oracle-SAS EG code

ChrisHemedinger — Tue, 20 Mar 2012 12:43:07 GMT

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