BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kgot
Fluorite | Level 6

I have a PROC SQL that runs a Connection to Oracle. If I run this SQL statement in a SQL-specific program, the code runs just fine. However, I get an "Invalid SQL Statement" error when I run it in SAS.

In attempting to diagnose why, I copy/pasted the block of SQL from the SAS log and tried to run it that way, thinking the formatting of the code itself might have something to do with it. It looks like it does; when I run what the SAS log produces (I am assuming this is how SAS itself processes it), the SQL program produces the same error.

Is this an accurate conclusion? Also, if this is the case, is there a way to prevent that from happening?

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Doc_Duke
Rhodochrosite | Level 12

The '02-sep-2014' is Oracle specific.  You need to follows SASKiwi's advice and use the pass-thru facility.

View solution in original post

7 REPLIES 7
SASKiwi
PROC Star

Most likely you need to run the SQL statement in a SAS SQL pass-through step as it may contain Oracle-specific syntax. Please code your code so we can advise further.

Steelers_In_DC
Barite | Level 11

I agree any code would be helpful, anytime you are passing through to any other environment there can be different nuances in how things should be written.  Teradata case statements always throw me off.

kgot
Fluorite | Level 6

The issue came in where I had multiple nested and/or statements in the WHERE clause. This is essentially what the statement in question looks like:

    where

        x.rownum=1

        and((X.nbr_fld=123456 and X.dt_fld>='02-SEP-2014')

        or (X.nbr_fld=234567 and X.dt_fld>='02-SEP-2014')

        or (X.nbr_fld=345678 and X.dt_fld>='02-SEP-2014')

        or (X.nbr_fld=111222 and X.dt_fld>='02-SEP-2014')

        or (X.nbr_fld=222333 and X.dt_fld>='02-SEP-2014')

        or (X.nbr_fld=123123 and X.dt_fld>='03-SEP-2014')

        or (X.nbr_fld=321321 and X.dt_fld>='03-SEP-2014')

        or (X.nbr_fld=432432 and X.dt_fld>='03-SEP-2014') ..... )

Because of the nature of the data I am working with, I have a number of these clauses (745, to be exact). Also, each date is particular to each serial number, so I am not sure if I could "hard code" any of these dates.

Doc_Duke
Rhodochrosite | Level 12

The '02-sep-2014' is Oracle specific.  You need to follows SASKiwi's advice and use the pass-thru facility.

kgot
Fluorite | Level 6

Thank you, Doc! I am very new to SAS, and I do not believe I have used the pass-thru facility--what do you mean by that?

kgot
Fluorite | Level 6

I got it! I was using pass-through logic already, so I looked further into the code to see if there was something I missed. Turns out there was.

The issue ended up being some commenting that I had in SQL using the "--" syntax. Since it seems SAS was processing the SQL statement as a block of text (instead of the line-by-line formatting I had), it was including some additional lines in the comment that I was not expecting. I took those parts out, and now it works just fine. Thank you Doc, Mark, and SASKiwi for helping me to get to the bottom of this!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 987 views
  • 3 likes
  • 4 in conversation