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. 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.
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, July 11, 2016
PRICE_C
_______
$8.00
$10.00
$12.00
$13.59
$13.99
$14.00
$27.98
$48.99
$54.00
quit;