Using the SAS_PUT( ) Function in the Teradata EDW

Implicit Use of the SAS_PUT( ) Function

After you install the formats that SAS supplies in libraries inside the Teradata EDW and publish any custom format definitions that you created in SAS, you can access the SAS_PUT( ) function with your SQL queries.

If the SQLMAPPUTTO= system option is set to SAS_PUT and you submit your program from a SAS session, the SAS SQL processor maps PUT function calls to SAS_PUT( ) function references that Teradata understands.

Note: If you specify SQLMAPPUTTO=database.SAS_PUT, database must be the same as the database where the SAS_PUT function is mapped.

This example illustrates how the PUT function is mapped to the SAS_PUT( ) function using implicit pass-through.

options sqlmapputto=sas_put;

libname dblib teradata user="sas" password="sas" server="sl96208"
   database=sas connection=shared;

   /*-- Set SQL debug global options --*/
   /*----------------------------------*/
   options sastrace=',,,d' sastraceloc=saslog;

   /*-- Execute SQL using Implicit Passthru --*/
   /*-----------------------------------------*/
   proc sql noerrorstop;
      title1 'Test SAS_PUT using Implicit Passthru ';
      select distinct
         PUT(PRICE,Dollar8.2) AS PRICE_C
            from dblib.mailorderdemo;
   quit;

These lines are written to the SAS log.

libname dblib teradata user="sas" password="sas" server="sl96208"
   database=sas connection=shared;

NOTE: Libref DBLIB was successfully assigned, as follows:
      Engine:        TERADATA
      Physical Name: sl96208

         /*-- Set SQL debug global options --*/
         /*----------------------------------*/
         options sastrace=',,,d' sastraceloc=saslog;

         /*-- Execute SQL using Implicit Passthru --*/
         /*-----------------------------------------*/
         proc sql noerrorstop;
           title1 'Test SAS_PUT using Implicit Passthru ';
           select distinct
              PUT(PRICE,Dollar8.2) AS PRICE_C
            from dblib.mailorderdemo
                ;

TERADATA_0: Prepared: on connection 0
SELECT * FROM sas."mailorderdemo"


TERADATA_1: Prepared: on connection 0
 select distinct cast(sas_put("sas"."mailorderdemo"."PRICE", 'DOLLAR8.2')
 as char(8)) as "PRICE_C" from "sas"."mailorderdemo"

TERADATA: trforc: COMMIT WORK
ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data.

TERADATA_2: Executed: on connection 0
 select distinct cast(sas_put("sas"."mailorderdemo"."PRICE", 'DOLLAR8.2')
 as char(8)) as "PRICE_C" from "sas"."mailorderdemo"

TERADATA: trget - rows to fetch: 9
TERADATA: trforc: COMMIT WORK

               Test SAS_PUT using Implicit Passthru                        9
                                        3:42 Thursday, September 25, 2010

                             PRICE_C
                             _______
                               $8.00
                              $10.00
                              $12.00
                              $13.59
                              $13.99
                              $14.00
                              $27.98
                              $48.99
                              $54.00

         quit;

Be aware of these factors:

The SQLMAPPUTTO= system option must be set to SAS_PUT to ensure that the SQL processor maps your PUT functions to the SAS_PUT( ) function and the SAS_PUT( ) reference is passed through to Teradata.
The SAS SQL processor translates the PUT function in the SQL SELECT statement into a reference to the SAS_PUT( ) function.
```
select distinct cast(sas_put("sas"."mailorderdemo"."PRICE", 'DOLLAR8.2')
   as char(8)) as "PRICE_C" from "sas"."mailorderdemo"
```
A large value, VARCHAR(n), is always returned because one function prototype accesses all formats. Use the CAST expression to reduce the width of the returned column to be a character width that is reasonable for the format that is being used.

The return text cannot contain a binary zero value (hexadecimal 00) because the SAS_PUT( ) function always returns a VARCHAR(n) data type and a Teradata VARCHAR(n) is defined to be a null-terminated string.

The SELECT DISTINCT clause executes inside Teradata, and the processing is distributed across all available data nodes. Teradata formats the price values with the $DOLLAR8.2 format and processes the SELECT DISTINCT clause using the formatted values.

Explicit Use of the SAS_PUT( ) Function

If you use explicit pass-through (a direct connection to Teradata), you can use the SAS_PUT( ) function call in your SQL program.

This example shows the same query from Implicit Use of the SAS_PUT( ) Functionand explicitly uses the SAS_PUT( ) function call.

proc sql noerrorstop;
   title1 'Test SAS_PUT using Explicit Passthru';
   connect to teradata (user=sas password=XXX database=sas server=sl96208);

   select * from connection to teradata
      (select distinct cast(sas_put("PRICE",'DOLLAR8.2') as char(8)) as
         "PRICE_C" from mailorderdemo);

disconnect from teradata;
quit;

The following lines are written to the SAS log.

 proc sql noerrorstop;
 title1 'Test SAS_PUT using Explicit Passthru ';
 connect to teradata (user=sas password=XXX database=sas server=sl96208);

 select * from connection to teradata
    (select distinct cast(sas_put("PRICE",'DOLLAR8.2') as char(8)) as
        "PRICE_C" from mailorderdemo);

            Test SAS_PUT using Explicit Passthru                        10
                                        13:42 Thursday, September 25, 2010

                          PRICE_C
                          _______
                            $8.00
                           $10.00
                           $12.00
                           $13.59
                           $13.99
                           $14.00
                           $27.98
                           $48.99
                           $54.00

disconnect from teradata;
quit;

Note: If you explicitly use the SAS_PUT( ) function in your code, it is recommended that you use double quotation marks around a column name to avoid any ambiguity with the keywords. For example, if you did not use double quotation marks around the column name, DATE, in this example, all date values would be returned as today's date.

select distinct
   cast(sas_put("price", 'dollar8.2') as char(8)) as "price_c",
   cast(sas_put("date", 'date9.1') as char(9)) as "date_d",
   cast(sas_put("inv", 'best8.') as char(8))  as "inv_n",
   cast(sas_put("name", '$32.') as char(32)) as "name_n"
from mailorderdemo;

Tips When Using the SAS_PUT( ) Function in Teradata

Format widths greater than 256 can cause unexpected or unsuccessful behavior.
If a variable is associated with a $HEXw. format, SAS/ACCESS creates the DBMS table, and the PUT function is being mapped to the SAS_PUT( ) function, SAS/ACCESS assumes that variable is binary and assigns a data type of BYTE to that column. The SAS_PUT( ) function does not support the BYTE data type. Teradata reports an error that the SAS_PUT( ) function is not found instead of reporting that an incorrect data type was passed to the function. To avoid this error, variables that are processed by the SAS_PUT( ) function implicitly should not have the $HEXw. format associated with them. For more information, see Data Types and the SAS_PUT( ) Function.

If you use the $HEXw. format in an explicit SAS_PUT( ) function call, this error does not occur.
If you use the $HEXw. format in an explicit SAS_PUT( ) function call, blanks in the variable are converted to “20” but trailing blanks (blanks that occur when using a format width greater than the variable width) are trimmed. For example, the value “A ” (“A” with a single blank) with a $HEX4. format is written as 4120. The value “A” (“A” with no blanks) with a $HEX4. format is written as 41 with no blanks.