Understanding SAS Access ( Technology Specific: SAS)

SAS Access:

           Definition: SAS Access provides data access and data integration between SAS and Third party databases such as DB2, SQL Server & Teradata. It provides seamless & transparent data integration. It allows the user to view, extract, process, load and update data all within SAS Environment.      

       Type of Access:

i)                    SAS Access Implicit: SQL stmts are generated on your behalf and processed to Third party databases. In SAS, one could create a data step or PROC step that uses a Teradata table and SAS will translate what it can to SQL and process it on Teradata. This can be handy but not efficient.

Data Step:

Data Ex;

Set PLW.stup;

Where st.col1=’AP’;

Run;

Sample Proc (Non SQL)

PROC Means data = sch.tab1;

                Var col1;

Run;

ii)                  Explicit Pass thru:  SQL stmts are taken from your code & processed directly in Third party databases as defined. This one is preferred.  A connection can be declared on the fly within a PROC SQL stmt.

PROC SQL;

Connect to Teradata(User=”Userid”

Password=”password”

Database=

Server=

Mode=Teradata);

Create Table abc as;

Select * from connection to Teradata

(select tb11,tb12 from TableNM as tb1

);

Run; Quit;

In Explicit SAS Access

 SAS does

·         Passes the SQL exactly as written to Teradata

·         Must be sure that the SQL used is in the Teradata SQL syntax and not SAS SQL syntax

·         Many functions are the same but some different, Watch out for date format difference and how you code NULL Vs. missing

TERADATA does

·         Perform the SQL request if the syntax is correct

·         Otherwise the request fails

               

What should we do:

Ø  Use explicit SQL when possible

Ø  Use Teradata explain to ensure your query is running the way you intended

Ø  Use sampling when extracting data to build a complex query

Ø   Set the options in your code to validate what is being sent to Teradata

o   Options debug = dbms_timers

o   SASTRACE = ‘,,,d’

o   SASTRACELOC = SASlog no$stuffix;

o   Options fullstimers;

Ø  Use the UTILCONN_TRANSIENT = Yes

Ø  Check your logs, make sure the SQL sent to Teradata is what you expanded

Ø  Be careful that you are using SAS SQL syntax/fn in the implicit case and Teradata SQL syntax/fn in the explicit

What should we not:

Ø  Don’t create Cartesian products or cross joins in Teradata. This occurs when either no joins are defined or cross join is used in your query.

o   This return every records of table b joined to each records of table A

Ø  Don’t use Implicit SQl unless you are experienced enough to be sure that everything will be run on the Teradata Server and not cause a reasonable drain by pulling entire tables from Teradata to SAS servers you are working on.

Additional Information If Third party database is Teradata:

LDAP (Lightweight Directory Access protocol): It provides the ability to maintain ID’s and Password for multiple application in a single Database.  SAS access to Teradata using LDAP. User id is given as User = “ID@LDAP”

Thanks
Learner