SAS/ACCESS Software for Relational Databases: Reference

Processing Tips For SAS Users

Reading and Inserting to the Same Teradata Table

If you use SAS/ACCESS to read rows from a Teradata table and then attempt to insert these rows into the same table, you will hang (suspend) your SAS session.

Behind the scenes

a SAS/ACCESS connection requests a standard Teradata READ lock for the read operation.
a SAS/ACCESS connection then requests a standard Teradata WRITE lock for the insert operation.
the WRITE lock request suspends because the read connection already holds a READ lock on the table. Consequently, your SAS session hangs (is suspended).

Example: SAS Code That Hangs a Session

libname tra teradata user=kamdar password=ellis;
proc sql;
insert into tra.sametable 
   select * from tra.sametable;

In this example

SAS/ACCESS creates a read connection to Teradata to fetch the rows selected (select *) from TRA.SAMETABLE, requiring a standard Teradata READ lock; Teradata issues a READ lock.
SAS/ACCESS creates an insert connection to Teradata to insert the rows into TRA.SAMETABLE, requiring a standard Teradata WRITE lock. But, the WRITE lock request suspends--the table is locked already by the READ lock.
Your SAS/ACCESS session hangs.

Obviously, to avoid the situation described, do not submit this code. There is an alternative. You can add SAS/ACCESS locking options. These options modify Teradata's standard locking. For a usage example, see Example 3: Preventing a Hung SAS Session When Reading and Inserting to the Same Table.

Using a BY Clause to Order Query Results

SAS/ACCESS returns table results from a query in random order because Teradata returns the rows to SAS/ACCESS randomly. In contrast, traditional SAS processing returns SAS data set observations in the same order every run of your job. If maintaining row order is important, then you should add a BY clause to your SAS statements. A BY clause ensures consistent ordering of the table results from Teradata.

In the following example, a Teradata table, ORD, has columns NAME and NUMBER. The PROC PRINT examples illustrate consistent and inconsistent ordering in the display of the ORD table rows.

libname prt teradata user=kamdar password=ellis;

PROC PRINT Example 1: Inconsistent Ordering

  proc print data=prt.ORD; var name number; run;

If this statement is run several times, the ORD rows are likely to be arranged differently each time. Because SAS/ACCESS displays the rows in the order that Teradata returns them, that is, randomly.

PROC PRINT Example 2: More Consistent Ordering

  proc print data=prt.ORD; var name number; by name; run;

With this statement, PROC PRINT output is ordered according to the NAME value. However, on successive runs of the statement, display of rows with a different number and an identical name can vary.

PROC PRINT Display 1

Rita Calvin 2222 Rita Calvin 199

PROC PRINT Display 2

Rita Calvin 199 Rita Calvin 2222

PROC PRINT Example 3: Consistent Ordering

  proc print data=prt.ORD; var name number; by name number; run;

With this statement the ordering is always identical because every column is specified in the BY clause. Thus, your PROC PRINT output always looks the same.

Replacing PROC SORT with a BY Clause

In general, PROC SORT steps are not useful to output a Teradata table. In traditional SAS processing, PROC SORT is used to order observations in a SAS data set. Subsequent SAS steps that use the sorted data set receive and process the observations in the sorted order. Teradata will not store output rows in the sorted order. Consequently, do not sort rows with PROC SORT if the destination sorted file is a Teradata table.

The examples that follow modify traditional SAS code for SAS/ACCESS for Teradata.

Example 1: A Traditional PROC SORT Job

libname sortprt '.';
proc sort data=sortprt.salaries; by income;
proc print data=sortprt.salaries;

Example 1 illustrates a PROC SORT statement found in typical SAS processing. This statement is useless in SAS/ACCESS for Teradata.

Example 2: A Sort Job Modified for SAS/ACCESS for Teradata

libname sortprt teradata user=kamdar password=ellis;
proc print data=sortprt.salaries; by income;

Example 2 removes the PROC SORT statement shown in Example 1. Instead, it uses a BY clause with PROC PRINT. The BY clause returns Teradata rows ordered by the INCOME column.

Chapter Contents
Previous
Next
Top of Page