This tutorial explains how to connect to teradata using SAS. It is an efficient approach to work with teradata tables as we are telling SAS to connect to teradata and run the code in the teradata server directly.
Important Teradata Functions inside SAS
![]() |
Connect to Terdata with SAS |
Write queries with Teradata SQL syntax
In simple words, we are creating 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.
In simple words, we are creating 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;Note :
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;
- user = provide username of your teradata account.
- password = provide password of your teradata account.
- server = provide server name
Creating Teradata Volatile Tables
The EXECUTE BY teradata method works for creating 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.
The following code would work using with or without EXECUTE BY function.
qualify rank() over ( partition by region order by income desc ) = 1
- QUALIFY - similar to HAVING clause
- RANK()- rank values
- OVER - define the criteria
- PARTITION - similar to GROUP BY
- ROW_NUMBER - row number (similar to _N_ in data step)
Second Method to Use Teradata Table in SAS
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 Statement for Teradata
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 Statement for Teradata
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 :
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.
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"Step 2 : Create the volatile table with EXECUTE BY teradata method. See the detailed code specified in the article above.
connection=global dbmstemp=yes;
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.
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,
ReplyDeleteThanks Richa for spreading the word. Cheers!
DeleteAwesome Depanshu,I would say please keep on writing such helpful posts
ReplyDeleteI 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.
ReplyDeletedo u have any idea on code migrating from sas to python?
ReplyDeleteA 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.
ReplyDeletehi extraordinary work bro, but just try to post connect to oracle from oracle
ReplyDelete