Using the SAS_PUT( ) Function in the DB2 Database

Implicit Use of the SAS_PUT( ) Function

After you install the formats that SAS supplies in libraries inside the DB2 database 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 DB2 understands.

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

%let mapconn=user=sas1 password=sas31 database=indb;
libname dblib db2 &mapconn;

data dblib.shoes;
set sashelp.shoes;
run;

options sastrace=',,,d' sastraceloc=saslog;

proc sql noerrorstop;
title 'Test SAS_PUT using Implicit PassThru/LIBNAME ';
select distinct
       PUT(SALES, Dollar8.2)AS SALES_C from dblib.SHOES;
quit;

These lines are written to the SAS log.

1726   options sastrace=',,,d' sastraceloc=saslog;
1727
1728   proc sql noerrorstop;
1729   title 'Test SAS_PUT using Implicit PassThru/LIBNAME ';
1730   select distinct
1731          PUT(SALES, Dollar8.2)AS SALES_C from dblib.SHOES;
DB2: AUTOCOMMIT turned ON for connection id 0 1854 1309265953 setconlo 0 SQL
  1855 1309265953 du_prep 0 SQL
DB2_363: Prepared: on connection 0 1856 1309265953 du_prep 0 SQL
SELECT * FROM SHOES FOR READ ONLY  1857 1309265953 du_prep 0 SQL
  1858 1309265953 du_prep 0 SQL
DB2: COMMIT performed on connection 0. 1859 1309265953 du_comm 0 SQL
  1860 1309265953 du_prep 0 SQL
DB2_364: Prepared: on connection 0 1861 1309265953 du_prep 0 SQL
 select distinct cast(SAS_PUT(TXT_1."SALES", 'DOLLAR8.2') as char(8)) 
      as SALES_C from SHOES TXT_1
FOR READ ONLY  1862 1309265953 du_prep 0 SQL
  1863 1309265953 du_prep 0 SQL
DB2: COMMIT performed on connection 0. 1864 1309265953 du_comm 0 SQL
  1865 1309265953 du_exec 0 SQL
DB2_365: Executed: on connection 0 1866 1309265953 du_exec 0 SQL
Prepared statement DB2_364 1867 1309265953 du_exec 0 SQL
  1868 1309265953 du_exec 0 SQL
ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data.  
    1869 1309265953 fetch 0
SQL
1732   quit;

Be aware of these items:

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 DB2.
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("dblib"."shoes"."SALES", 'DOLLAR12.2')
   as char(12)) as "SALES_C" from "dblib"."shoes"
```
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 DB2 VARCHAR(n) is defined to be a null-terminated string.

The SELECT DISTINCT clause executes inside DB2, and the processing is distributed across all available data nodes. DB2 formats the sales values with the $DOLLAR12.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 (direct connection to DB2), 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( ) Function and explicitly uses the SAS_PUT( ) function call.

%let mapconn=user=sasts password=xxxx database=indb;
libname dblib db2 &mapconn;

data dblib.shoes;
set sashelp.shoes;
run;

options sastrace=',,,d' sastraceloc=saslog;

proc sql noerrorstop;
title 'Test SAS_PUT using Explicit Passthru ';
connect to db2 (user=sas3 password=sas31 database=indb);
select * from connection to db2
  (select distinct (sas_put("SALES",'DOLLAR12.2')) as "SALES_C" from SHOES);
disconnect from db2;
quit;

The following lines are written to the SAS log.

1733
1734   proc sql noerrorstop;
1735   title 'Test SAS_PUT using Explicit Passthru ';
1736   connect to db2 (user=db2 password=XXXXXXXXXXXX database=indb);
DB2: AUTOCOMMIT is YES for connection 4 1870 1309265953 ducon 0 SQL
1737   select * from connection to db2
1738     (select distinct (sas_put("SALES",'DOLLAR12.2')) as "SALES_C" from
SHOES);
  1871 1309265953 du_prep 0 SQL
DB2_366: Prepared: on connection 4 1872 1309265953 du_prep 0 SQL
select distinct (sas_put("SALES",'DOLLAR12.2')) as "SALES_C" from SHOES 1873
1309265953 du_prep 0
SQL
  1874 1309265953 du_prep 0 SQL
DB2: COMMIT performed on connection 4. 1875 1309265953 du_comm 0 SQL
  1876 1309265953 du_exec 0 SQL
DB2_367: Executed: on connection 4 1877 1309265953 du_exec 0 SQL
Prepared statement DB2_366 1878 1309265953 du_exec 0 SQL
  1879 1309265953 du_exec 0 SQL
1739   disconnect from db2;
1740   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("sales", 'dollar12.2') as char(12)) as "sales_c",
   from shoes;