BookmarkSubscribeRSS Feed
Scott86
Obsidian | Level 7

Hi Everyone,

 

I work with SAS enterprise guide a lot and I often run into the issue of not knowing what code I can run on oracle. We do a lot of table joins on tables from oracle (we are moving to teradata) and I often get errors I don't understand in my SQL create table statements. I think you can't use SAS sql on oracle and you have to use ANSI SQL, this also changes the functions you can use. We use a pass through statement to connect to oracle.

 

Does anyone know any good resources (text books or websites) to up skill on this?

 

Thankss

 

5 REPLIES 5
Tom
Super User Tom
Super User

Every SQL implementation is different. Even those that claim to support ANSI standards.

 

PROC SQL in SAS is based on an older SQL standard so many newer features of SQL language are not supported. But that means that in general SQL that runs in PROC SQL will run in most implementations of SQL.  The main trouble you will have is when you use use SAS specific functions or special SAS syntax like dataset options or FORMAT=, INFORMAT= and LABEL= tags when defining fields.

 

If you have SAS/Access to xxxx (Oracle or Teradata) then you can try just writing your code in SAS and letting SAS translate it into the database specific syntax for you.

 

 

SASKiwi
PROC Star

If you are more familiar with PL-SQL in Oracle then I suggest you stick with SAS SQL passthru as you can simply paste your PL-SQL into the passthru query. Passthru is the most effective way to fine tune Oracle queries as you can add performance optimisation like Oracle hints for parallel processing.

Reeza
Super User

You're basically needing to learn Oracle SQL now, so you should find an Oracle SQL forum and resources. 

Patrick
Opal | Level 21

@Scott86

When interfacing with a database you've got basically two options:

Option A: You write your SQL in SAS SQL and have the SAS/Access engine translate this SQL to the database specific flavor during runtime.

Option B: You use explicit pass-through SQL and write the SQL code in the database flavor. SAS will then just send this SQL code as-is to the database for execution.

 

The advantage of Option A is that you won't have to change your SAS SQL code when moving from Oracle to Teradata (if table names don't change). You just use a different SAS/Access engine so it's just a change to the libname statement.

The disadvantage of Option A: You might write SAS SQL code which the SAS/Access engine can't fully translate into the database SQL flavor. This can result in the process to pull down huge amount of data to SAS as whatever SQL logic couldn't get pushed to the database needs to execute on the SAS side and for this the data needs to get moved to the SAS side.

N.B: It is documented what and what not SAS can push to a database for execution. ...and it depends both on the database and the SAS version.

 

The advantage of Option B: You have full control over the SQL code executed on the database and you can use database specific functionality.

The disadvantage of Option B: You have to learn the database specific SQL flavor and your SQL needs change if moving to another database vendor.

 

 

 

 

 

 

kiranv_
Rhodochrosite | Level 12

@Patrick answers sums up everything .  I just wants to add couple of things on conversion of Oracle to Teradata part. I have been on project where in moved from Oracle to Teradata.  We had to change lot of scripts especially related to explicit pass through. If you know basic SQL then Oracle SQL or Teradata SQL should not big deal. Many functions are different in both the SQL, but google is your friend here. One thing you have to be careful is how index are created and used in both of this databases, which is really big deal. In Teradata, primary index should be designed carefully otherwise data skewing  will happen. Data skew causes space wastage and also weakens the parallel processing capabilities of Teradata. Another difference for explicit pass through is Oracle is case sensitive and Teradata is not case sensitive

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
  • 5 replies
  • 1076 views
  • 3 likes
  • 6 in conversation