Using the SAS_PUT( ) Function in Greenplum

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

/* 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:

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
*/

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;