BookmarkSubscribeRSS Feed
renjithr
Quartz | Level 8

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

7 REPLIES 7
SASKiwi
PROC Star

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.

renjithr
Quartz | Level 8

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?

Doc_Duke
Rhodochrosite | Level 12

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

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
SASKiwi
PROC Star

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.

LinusH
Tourmaline | Level 20

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
Gozzi78
SAS Employee

Hi renjithr ,

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

Cheers,

Gozzi78

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1548 views
  • 0 likes
  • 6 in conversation