Using the SAS_PUT( ) Function in Greenplum

Implicit Use of the SAS_PUT( ) Function

After you install the formats that SAS supplies in libraries inside the Greenplum data warehouse 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 (the default) and you submit your program from a SAS session, the SAS SQL processor maps PUT function calls to SAS_PUT( ) function references that Greenplum understands.

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

/* implicit pass-thru query */
options sqlgeneration=dbms sqlreduceput=none;
options sastrace=',,,d' sastraceloc=saslog
        sql_ip_trace=(note,source) msglevel=i;

proc sql noerrorstop reduceput=none details="reduce_put_bench$";
create table fmt_ipout as
   select distinct id, put(a,ANIMAL.) len=50 as fmtresult
     from dblib.sample ; 
quit; 
options sastrace=',,,,' 
        sql_ip_trace=none msglevel=n;

This is a partial listing of the lines that are written to the SAS log.

/*
GREENPL_1: Prepared:     
SELECT * FROM SAMPLE FOR READ ONLY 

NOTE: XOG: Put Ping Query
NOTE: SELECT SAS_PUT('ANIMAL', '$IS-INTRINSIC') AS X, SAS_PUT('ANIMAL', 
      '$FMT-META') AS Y FROM (SELECT COUNT(*) AS C FROM  SAMPLE WHERE 0=1) 

GREENPL_2: Prepared:
select distinct TXT_1."id", cast(SAS_PUT(TXT_1."a", 'ANIMAL20.0') as char(20))
   as fmtresult from SAMPLE TXT_1

SQL_IP_TRACE: pushdown attempt # 1
SQL_IP_TRACE: passed down query:    
select distinct TXT_1."id", cast(SAS_PUT(TXT_1."a", 'ANIMAL20.0') as char(20))
   as fmtresult from SAMPLE TXT_1
SQL_IP_TRACE: The SELECT statement was passed to the DBMS.

GREENPL_3: Executed:
Prepared statement GREENPL_2
     
ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data.
*/

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 Greenplum.
The SAS SQL processor translates the PUT function in the SQL SELECT statement into a reference to the SAS_PUT( ) function.
```
select distinct TXT_1."id",
   cast(SAS_PUT(TXT_1."a", 'ANIMAL20.0') as char(20)) as fmtresult
   from SAMPLE TXT_1
```
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 Greenplum VARCHAR(n) is defined to be a null-terminated string.

The SELECT DISTINCT clause executes inside Greenplum, and the processing is distributed across all available data nodes. Greenplum formats the id values with the ANIMAL 20.0 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 Greenplum), 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.

options sastrace=',,,d' sastraceloc=saslog
        sql_ip_trace=(note,source) msglevel=i;

proc sql noerrorstop;
connect to greenplm (&exconn) ;
create table fmt_epout as
select * from connection to greenplm (
select id, sas_put(a,'ANIMAL' ) as FMTRESULT
from sample
);
quit;
options sastrace=',,,,' 
        sql_ip_trace=none msglevel=n;

This is a partial listing of the lines that are written to the SAS log.

/*
GREENPL_4: Prepared: 
select id, sas_put(a,'ANIMAL' ) as FMTRESULT from sample

GREENPL_5: Executed: 
Prepared statement GREENPL_4
*/

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("id", 'animal20.0') as char(20)) as "id",
   from sample;