01-26-2018 08:20 PM
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?
01-26-2018 09:05 PM
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.
01-26-2018 09:22 PM
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.
01-27-2018 09:33 PM
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.
01-31-2018 11:56 PM
@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