Connect to Teradata using SAS

Deepanshu Bhalla 7 Comments

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.

Connect to Terdata with SAS
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
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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.

7 Responses to "Connect to Teradata using SAS"
  1. its a great site Deepanshu,this site has helped many people in understanding SAS from The very basic,whenever i get stucked anywhere i take help from here.Thanks for making such a wonderful site.I keep recommending to people about this site.Really good Good job,

    ReplyDelete
  2. Awesome Depanshu,I would say please keep on writing such helpful posts

    ReplyDelete
  3. I have a doubt,when I try accessing a teradata table from sas,I dont see all the variables because of the data type.How can I list all the variables of teradata table while accessing from sas.

    ReplyDelete
  4. do u have any idea on code migrating from sas to python?

    ReplyDelete
  5. A very extraordinary site. The material presented is very easy to understand for a beginner. It will be very helpful if the material is presented in pdf format. Thank you Deepanshu.

    ReplyDelete
  6. hi extraordinary work bro, but just try to post connect to oracle from oracle

    ReplyDelete
Next → ← Prev