SAS/ACCESS Software for Relational Databases: Reference

SQL Procedure Pass-Through Facility: Teradata Specifics

You can use the pass-through facility of PROC SQL to build your own Teradata SQL statements and then pass them to the Teradata server for execution. The PROC SQL CONNECT statement defines the connection between SAS and the Teradata DBMS. See Chapter 6, "SQL Procedure's Interaction with SAS/ACCESS Software" .

The following section describes the DBMS-specific arguments that you use in the CONNECT statement to establish a connection with a Teradata database.

Arguments to Connect to Teradata

The SAS/ACCESS Interface to Teradata can connect to multiple Teradata servers and to multiple Teradata databases. However, if you use multiple, simultaneous connections, you must use an alias argument to identify each connection.

CAUTION:: Do not issue a Teradata DATABASE statement within the EXECUTE statement in PROC SQL. Use the SAS/ACCESS SCHEMA= option if you must change the default Teradata database.

The CONNECT statement uses SAS/ACCESS connection options. For more information, see SCHEMA= and SAS/ACCESS Engine Connection Options.

USER and PASSWORD are the only required options.

CONNECT TO TERADATA <AS alias> (USER=TERADATA-user-name
PASSWORD=TERADATA-password
<TDPID=dbcname
SCHEMA=alternate-database
ACCOUNT=account_ID>);

USER=<'>Teradata-user-name<'>: specifies a Teradata user name. You must also specify PASSWORD=.
PASSWORD= | PASS= | PW= <'>Teradata-password<'>: specifies the Teradata password that is associated with the Teradata user name. You must also specify USER=.

Pass-Through Examples

This section presents simple pass-through examples. If you need background information to understand any example, or need more sophisticated examples, see the PROC SQL documentation available from SAS Institute.

Example 1: Using the Alias DBCON for the Teradata Connection

proc sql;
   connect to teradata as dbcon
           (user=kamdar pass=ellis);
 quit;

In Example 1, SAS/ACCESS

Connects to the Teradata DBMS using the alias dbcon.
Performs no other work.

Example 2: Deleting and Recreating a Teradata Table

proc sql;
   connect to teradata as tera ( user=kamdar password=ellis );
   execute (drop table salary) by tera;
   execute (create table salary (current_salary float, name char(10))) 
            by tera;
   execute (insert into salary values (35335.00, 'Dan J.')) by tera;
   execute (insert into salary values (40300.00, 'Irma L.')) by tera;
   disconnect from tera;
 quit;

In Example 2, SAS/ACCESS

Connects to the Teradata DBMS using the alias tera.
Drops the SALARY table.
Recreates the SALARY table.
Inserts two rows.
Disconnects from the Teradata DBMS.

Example 3: Updating a Teradata Table

 proc sql;
   connect to teradata as tera ( user=kamdar password=ellis );
   execute (update salary set current_salary=45000
            where (name='Irma L.')) by tera;
   disconnect from tera;
  quit;

In Example 3, SAS/ACCESS

Connects to the Teradata DBMS using the alias tera.
Updates the row for Irma L., changing her current salary to 45000.00.
Disconnects from the Teradata DBMS.

Example 4: Selecting and Displaying a Teradata Table

proc sql;
   connect to teradata as tera2 ( user=kamdar password=ellis );
   select * from connection to tera2 (select * from salary);
   disconnect from tera2;
  quit;

In Example 4, SAS/ACCESS

Connects to the Teradata database using the alias tera2.
Selects all rows in the SALARY table and displays them using PROC SQL.
Disconnects from the Teradata database.

Chapter Contents
Previous
Next
Top of Page