SAS/ACCESS Interface to Teradata

Teradata Processing Tips for SAS Users

Reading from 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 can hang (suspend) your SAS session.

Here is what happens:

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).

Here is what happens in the next 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 because the table is locked already by the READ lock.
Your SAS/ACCESS session hangs.

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

To avoid this situation, use the SAS/ACCESS locking options. For details, see Locking in the Teradata Interface.

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 during 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 this example, the Teradata ORD table has NAME and NUMBER columns. The PROC PRINT statements illustrate consistent and inconsistent ordering when it displays ORD table rows.

libname prt teradata user=testuser password=testpass;

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

If this statement is run several times, it yields inconsistent ordering, meaning that ORD rows are likely to be arranged differently each time. This happens because SAS/ACCESS displays the rows in the order in which Teradata returns them--that is, randomly.

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

This statement achieves more consistent ordering because it orders PROC PRINT output by the NAME value. However, on successive runs of the statement, rows display of rows with a different number and an identical name can vary, as shown here.

PROC PRINT Display 1

Rita Calvin 2222
Rita Calvin 199

PROC PRINT Display 2

Rita Calvin 199
Rita Calvin 2222

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

The above statement always yields identical ordering because every column is specified in the BY clause. So your PROC PRINT output always looks the same.

Using TIME and TIMESTAMP

This example creates a Teradata table and assigns the SAS TIME8. format to the TRXTIME0 column. Teradata creates the TRXTIME0 column as the equivalent Teradata data type, TIME(0), with the value of 12:30:55.

libname mylib teradata user=testuser password=testpass;

data mylib.trxtimes;
   format trxtime0 time8.;
   trxtime0 = '12:30:55't;
run;

This example creates a Teradata column that specifies very precise time values. The format TIME(5) is specified for the TRXTIME5 column. When SAS reads this column, it assigns the equivalent SAS format TIME14.5.

libname mylib teradata user=testuser password=testpass;

proc sql noerrorstop;
   connect to teradata (user=testuser password=testpass);
   execute (create table trxtimes (trxtime5 time(5) 
      )) by teradata;
   execute (commit) by teradata;
   execute (insert into trxtimes
            values (cast('12:12:12' as time(5))
      )) by teradata; 
   execute (commit) by teradata;
quit;

/* You can print the value that is read with SAS/ACCESS. */
proc print data =mylib.trxtimes;
run;

SAS might not preserve more than four digits of fractional precision for Teradata TIMESTAMP.

This next example creates a Teradata table and specifies a simple timestamp column with no digits of precision. Teradata stores the value 2000-01-01 00:00:00. SAS assigns the default format DATETIME19. to the TRSTAMP0 column generating the corresponding SAS value of 01JAN2000:00:00:00.

proc sql noerrorstop; 
   connect to teradata (user=testuser password=testpass);
   execute (create table stamps (tstamp0 timestamp(0)
      )) by teradata;
   execute (commit) by teradata;
   execute (insert into stamps
            values (cast('2000-01-01 00:00:00' as
                    timestamp(0))
      )) by teradata;
   execute (commit) by teradata;
quit;

This example creates a Teradata table and assigns the SAS format DATETIME23.3 to the TSTAMP3 column, generating the value 13APR1961:12:30:55.123. Teradata creates the TSTAMP3 column as the equivalent data type TIMESTAMP(3) with the value 1961-04-13 12:30:55.123.

libname mylib teradata user=testuser password=testpass;

data mylib.stamps;
format tstamp3 datetime23.3;
tstamp3 = '13apr1961:12:30:55.123'dt;
run;

This next example illustrates how the SAS engine passes the literal value for TIMESTAMP in a WHERE statement to Teradata for processing. Note that the value is passed without being rounded or truncated so that Teradata can handle the rounding or truncation during processing. This example would also work in a DATA step.

proc sql ;
  select * from trlib.flytime where col1 = '22Aug1995 12:30:00.557'dt ;
  quit;

In SAS 8, the Teradata interface did not create TIME and TIMESTAMP data types. Instead, the interface generated FLOAT values for SAS times and dates. This example shows how to format a column that contains a FLOAT representation of a SAS datetime into a readable SAS datetime.

libname mylib teradata user=testuser password=testpass;

proc print data=mylib.stampv80;
format stamp080 datetime25.0;
run;

Here, the old Teradata table STAMPV80 contains the FLOAT column, STAMP080, which stores SAS datetime values. The FORMAT statement displays the FLOAT as a SAS datetime value.

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 does not store output rows in the sorted order. Therefore, do not sort rows with PROC SORT if the destination sorted file is a Teradata table.

The following example illustrates a PROC SORT statement found in typical SAS processing. You cannot use this statement in SAS/ACCESS Interface to Teradata.

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

This example removes the PROC SORT statement shown in the previous example. It instead uses a BY clause with a VAR clause with PROC PRINT. The BY clause returns Teradata rows ordered by the INCOME column.

libname sortprt teradata user=testuser password=testpass;
proc print data=sortprt.salaries; 
var income;
by income;

Reducing Workload on Teradata by Sampling

The OBS= option triggers SAS/ACCESS to add a SAMPLE clause to generated SQL. In this example, 10 rows are printed from dbc.ChildrenX:

Libname tra teradata user=sasdxs pass=****** database=dbc; 
Proc print data=tra.ChildrenX (obs=10);
run;

The SQL passed to Teradata is:

SELECT "Child","Parent" FROM "ChildrenX"  SAMPLE 10

Especially against large Teradata tables, small values for OBS= reduce workload and spool space consumption on Teradata and your queries complete much sooner. See the SAMPLE clause in your Teradata documentation for further information.

Top of Page