Connect to Teradata using SAS

This tutorial explains how to connect to teradata using SAS.

The efficient approach to work with Teradata tables is to connect to Teradata using SAS and run the code directly on the Teradata server.

Write Teradata SQL Syntax

In simple words, we are writing Teradata SQL statements and then pass them to the Teradata server for execution. Only Teradata SQL functions would work within "connection to teradata" code. For example, INPUT PUT functions would not work. Instead, cast function would be used for changing variable type.

proc sql;
   connect to teradata (user="youruserid" password="yourpassword" server="servername" mode=teradata);
   create table temp as
   select * from connection to teradata (
      select a.ID
           , a.Product
           , b.Income
      from tdbase.customer a
      join tdbase.income b
      on a.ID=b.ID
      );
  disconnect from teradata;
quit;

Note :

1. user = provide username of your teradata account.
2. password = provide password of your teradata account.
3. server = provide server name

Creating Teradata Volatile Tables

The EXECUTE BY teradata method works for creating volatile tables.

proc sql;
 connect to teradata (user="youruserid" password="yourpassword" mode=teradata  server="servername" connection=global);
 execute(
 create volatile table temp as (
 select id
 , region
 , sector
 , income
 from ls_policy_inter
 group by 1,2
 )
 with data primary index (id)
 on commit preserve rows
 ) by teradata;
quit;

Important Teradata Functions inside SAS

Many teradata statements and functions work only inside EXECUTE BY teradata method. For example, RENAME teradata table does not work without EXECUTE BY.

The following code would work using with or without EXECUTE BY function.

qualify rank() over ( partition by region order by income desc ) = 1

1. QUALIFY - similar to HAVING clause
2. RANK()- rank values
3. OVER - define the criteria
4. PARTITION - similar to GROUP BY
5. ROW_NUMBER - row number (similar to _N_ in data step)

Method 2 : Access Teradata Table Using LIBNAME Statement

We can also access to teradata table with LIBNAME statement. In this case, we are not hitting teradata server and sql query would be run on sas environment,

libname foo teradata server="servername" user=”youruserid” password=”yourpassword”;

Note : In the code above, foo is a library name in which teradata table would be stored.

How to access teradata volatile tables in SAS

Suppose you are writing a lengthy code in which you need to create a lot of volatile tables and access these tables in the following (subsequent) steps in SAS.

Step 1 : Prior to creating volatile table, you first have to create a reference of your library with libname statement and including CONNECTION=GLOBAL and DBMSTEMP=YES options.

Check out the code below :

libname tdref  teradata user="userid" password="password" mode=teradata  server="servername"     connection=global dbmstemp=yes;

Step 2 : Create the volatile table with EXECUTE BY teradata method. See the detailed code specified in the article above.

Step 3 : Look at the volatile table that you created in TDREF library. You can refer these tables in the later SAS code as TDREF.DATASET-NAME

Which method is more efficient?

The first method "CONNECT TO TERADATA" is more efficient than the second method - LIBNAME statement as the first method hits the tables in teradata server and it would take less execution time. However, the sas functions such as INPUT, PUT, INTCK etc do not work inside the CONNECT TO TERADATA sql query. In the second method : LIBNAME Statement, all the sas functions and data step work.

Related Posts

SAS Tutorials : Top 100 SAS Tutorials

Spread the Word!
Share Share Tweet

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn