You can access Teradata tables with SAS/ACCESS Interface to Teradata software using either the Teradata LIBNAME engine or the Pass-Through Facility with the SQL procedure. A LIBNAME engine provides a seamless and transparent way to surface data. It requires minimal knowledge of the database and Structured Query Language (SQL). Once you define the LIBNAME you can use Teradata tables the same as a SAS dataset.

You need to substitute your own Teradata connection information for teradata-user, teradata-password, and teradata-server.

Once you assign the LIBNAME, you can view your Teradata tables in your library in the SAS Explorer window or, if you prefer, you can submit a DATASETS procedure that is similar to the following to display the tables in the SAS log.

In the following example, the KEEP data set option causes the SAS/ACCESS engine to select only the SALARY and DEPT columns when it reads the teralib.EMPLOYEES table:

The PROC SQL Pass-Through Facility requires that you have knowledge of the SQL that is passed to the database via PROC SQL.

In the above example, the second SELECT statement (in the parentheses) is passed to Teradata 'as is'. A temporary SAS dataset ‘job204’ is created from the Teradata table employees containing jobcode 204.

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	SAS/ACCESS Interface to Teradata	Microsoft Windows 95/98	9.21_M2
		Microsoft Windows 2000 Advanced Server	9.21_M2
		Microsoft Windows 2000 Datacenter Server	9.21_M2
		Microsoft Windows 2000 Server	9.21_M2
		Microsoft Windows 2000 Professional	9.21_M2
		Microsoft Windows NT Workstation	9.21_M2
		Microsoft Windows Server 2003 Datacenter Edition	9.21_M2
		Microsoft Windows Server 2003 Enterprise Edition	9.21_M2
		Microsoft Windows Server 2003 Standard Edition	9.21_M2
		Microsoft Windows Server 2003 for x64	9.21_M2
		Microsoft Windows Server 2008	9.21_M2
		Microsoft Windows Server 2008 for x64	9.21_M2
		Microsoft Windows XP Professional	9.21_M2
		Windows 7 Enterprise 32 bit	9.21_M2
		Windows 7 Enterprise x64	9.21_M2
		Windows 7 Home Premium 32 bit	9.21_M2
		Windows 7 Home Premium x64	9.21_M2
		Windows 7 Professional 32 bit	9.21_M2
		Windows 7 Professional x64	9.21_M2
		Windows 7 Ultimate 32 bit	9.21_M2
		Windows 7 Ultimate x64	9.21_M2
		Windows Millennium Edition (Me)	9.21_M2
		Windows Vista	9.21_M2
		Windows Vista for x64	9.21_M2
		64-bit Enabled AIX	9.21_M2
		64-bit Enabled HP-UX	9.21_M2
		64-bit Enabled Solaris	9.21_M2
		HP-UX IPF	9.21_M2
		Linux	9.21_M2
		Linux for x64	9.21_M2
		Solaris for x64	9.21_M2

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

This code will not run successfully as 'stand-alone' since you need to substitute your own Teradata connection information for teradata-user, teradata-password, and teradata-server.

---

libname teralib teradata server=server user=id pwd=password;

Data work.acc024;
set teralib.employees(keep=salary dept);
where dept='ACC024';
run;

proc sql;                                                                                                                               
connect to teradata(server=server user=id pwd=password);                                                                              
create table work.job204 as                                                                                                             
select * from connection to teradata                                                                                                    
(select * from employees where jobcode=204);                                                                                            
quit;                                                                                                                                   
     

107
108  libname teralib teradata server=sl91203 user=sas pwd=XXX;
NOTE: Libref TERALIB was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: sl91203
109
110  Data work.acc024;
111  set teralib.employees(keep=salary dept);
112  where dept='ACC024';
113  run;

NOTE: There were 3 observations read from the data set TERALIB.employees.
      WHERE dept='ACC024';
NOTE: The data set WORK.ACC024 has 3 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.11 seconds
      cpu time            0.01 seconds


114
115  proc sql;
116  connect to teradata(user=sas password=XXX server=sl91203);
117  create table work.job204 as
118  select * from connection to teradata
119  (select * from employees where jobcode=204);
NOTE: Table WORK.JOB204 created, with 7 rows and 11 columns.

120  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.03 seconds


NOTE: This SAS session is using a registry in WORK.  All changes will be lost at the end of this
      session.
NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 5 table(s) have not been
      displayed/returned.

Sample of LIBNAME and PROC SQL Pass-Through code to connect to a Teradata database using SAS/ACCESS Interface to Teradata.

Type:	Usage Note
Priority:
Topic:	Data Management ==> Data Sources ==> External Databases ==> Teradata

Date Modified:	2010-12-24 12:32:54
Date Created:	2010-11-17 16:45:58