Using a Teradata UDF in SAS Implicit Sql Pass Thru

[pchegoor]

I am trying to use a Teradata UDF (User Defined Function) in a SAS Implicit SQL which establishes the connection to Teradata using LIBNAME Statement. Assume that the function is called PTY_DECRYPT and is defined in a Database called TEST in Teradata. The Purpose of this function is to decrypt values in a Column of a View in Teradata.

What works is using the UDF in an Explicit Sql .Below I am using the function on a column called SSN_NBR in a view called V_TEST_PERS present in the Database called SAMPLE.

Explcit Sql (This Works!):

Options debug=DBMS_TIMERS sastrace=',,,d'
sastraceloc=saslog no$stsuffix fullstimer;


Proc Sql;
Connect to TERADATA(User=XXXXX pwd=XXXXX server=XXXXX);
Create Table Final as
select * from connection to teradata
(
Select
sub_id, 
SSN_NBR,
TEST.PTY_DECRYPT(SSN_NBR,'T_ssn_test',400,0,0 ) as SSN_NBR_Decrypt
from SAMPLE.V_TEST_PERS
);
disconnect from teradata;
Quit;

But I would like to use the same function in an Implicit SQL but it does not work.

Implicit Sql (This does not Work)

Options debug=DBMS_TIMERS sastrace=',,,d'
sastraceloc=saslog no$stsuffix fullstimer;

Libname Td Teradata User=XXXXX pwd=XXXXX server=XXXXX database=SAMPLE ;

Proc sql;
Create table Final as
select
sub_id, 
SSN_NBR,
TEST.PTY_DECRYPT(SSN_NBR,'T_ssn_test',400,0,0 ) as SSN_NBR_Decrypt
from Td.V_TEST_PERS;
Quit;

 Any ideas as  to how to make  this  Function work in Implicit Sql  with minimum changes?  

I  am using SAS 9.4M3 on AIX 64bit .   Not sure if there are any  SAS/ACCESS Libname specific Options which can do the trick.

Thanks.

[BrunoMueller]

Hi

It is possible to do this. Check this doc http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n0k1cax0gzsdxdn1a3...

Find below some old code sample I found, please note it is not tested, but should give you a starting point.

Here are the basic steps:

1. create a SAS function using Proc FCMP that has the same number and types of arguments, the function does not need anything to do, it is just so that the name is recognized
2. add the function information to a SAS data set
3. specifiy this data set with SQL_FUNCTIONS= option of the LIBNAME statement

/* First create an UDF function in Teradata called plusudf with bteq/sql query assistant*/
/*create dummysas function with FCMP procedure*/
proc fcmp outlib=work.funcs.trial;
  function dummysas(X, Y);
    return (n);
  endsub;
run;

options cmplib=work.funcs;

/* Test the new function in sas*/
data _null_;
  x=dummysas(1,2);
  put x=;
run;

/*add function to sql dictionnary*/
data work.newfunc;
  SASFUNCNAME = "dummysas";
  SASFUNCNAMELEN = 8;
  DBMSFUNCNAME = "plusudf";
  DBMSFUNCNAMELEN = 7;
  FUNCTION_CATEGORY = "SCALAR";
  FUNC_USAGE_CONTEXT = "SELECT_LIST";
  FUNCTION_RETURNTYP = "INTEGER";
  FUNCTION_NUM_ARGS = 2;
  CONVERT_ARGS = 0;
  ENGINEINDEX = 0;
  output;
run;

/*Test the UDF function in Teradata*/
libname financ teradata server=dbc schema="financial" user=tduser pwd=tduser 
  SQL_FUNCTIONS="EXTERNAL_APPEND=work.newfunc"
  SQL_FUNCTIONS_COPY= saslog
;

Bruno

[pchegoor]

@BrunoMueller       Thank You for your  Reply .

I tried your solution  but  i  do not  get  desired result .  Instead  i just get a blank value .

The  Teradata  UDF in my Original  example above is  in a separate Database in Teradata  for  eg  TEST  in the above case . The function name  being PTY_DECRYPT.  It  Takes  5  arguments ,  The first  2  arguments  are of type VARCHAR  and  Rest of the arguments of  type  INTEGER. The Function  also returns a VARCHAR of length 400.

So I have defined  the SAS Function  as shown below.

proc fcmp outlib=work.funcs.trial;
function dummysas(col1 $,Col2 $,Col3,Col4,Col5) $ 400;
return (n);
endsub;
run;

Next  the  Function  to  be added to  the Sql Dictionary  is defined as follows :

data work.newfunc;
SASFUNCNAME = "dummysas";
SASFUNCNAMELEN = 8;
DBMSFUNCNAME = "TEST.PTY_DECRYPT";
DBMSFUNCNAMELEN = 16;
FUNCTION_CATEGORY = "SCALAR";
FUNC_USAGE_CONTEXT = "SELECT_LIST";
FUNCTION_RETURNTYP = "VARCHAR";
FUNCTION_NUM_ARGS = 5;
CONVERT_ARGS = 0;
ENGINEINDEX = 0;
output;
run;

Finally i try  to use  the  Function above in a  Query  using  Implicit  Connection to Teradata.  The Function is being applied to a Column called SSN_NBR  in  the Teradata  view called  V_TEST_PERS  present in the Database called  SAMPLE.

Libname Td Teradata User=XXXXX pwd=XXXX  server=XXXXX schema=SAMPLE 
SQL_FUNCTIONS="EXTERNAL_APPEND=work.newfunc"
SQL_FUNCTIONS_COPY= saslog;

options cmplib=work.funcs;


Proc sql;
Create table Final as
select
sub_id, 
SSN_NBR,
dummysas(SSN_NBR,'T_ssn_test',400,0,0 ) as SSN_NBR_Decrypt
from Td.V_TEST_PERS;
Quit;

In the  Dataset  Final i get  the  Column  values of SSN_NBR_Decrypt  as Blank   for all observations . 

Also  i observe  that this column  has the same value  as that  returned by the  function dummysas   within the  PROC FCMP above.

For eg  if  i  make the function  return a contant  value  of  YES  then  the column  SSN_NBR_Decrypt as this  value of  YES  for all observervations.

Am i  doing anything wrong or is this not possible to implement via  Implicit Sql ?

[BrunoMueller]

hi

Did you check whether the function was passed down to the DBMS?

From your description it sounds like this did not happen.

Why not, I have no idea.

I used the example I showed to implement this as you describe, so that SAS Enterprise Guide users could use the query builder with the UDF function within the database.

Bruno

[BrunoMueller]

Hi

Just noticed the value for

SASFUNCNAMELEN = 9;

 should by

SASFUNCNAMELEN = 8;

maybe this helps

Bruno

[pchegoor]

@BrunoMueller       Sorry  actually   it  should have been

SASFUNCNAMELEN = 8;

instead  of 

SASFUNCNAMELEN =9;

I  have corrected it above.

But  it still does not work .I get the same blank value.  Tough luck.

Also  i did turn on the following  options   to see the how query is getting processed:

Options  debug=DBMS_TIMERS sastrace=',,,d'
sastraceloc=saslog no$stsuffix fullstimer ;

I  noticed that function is not getting passed to Teradata .  This  is what i see in the SAS Log :

TERADATA_6: Prepared: on connection 2
SELECT * FROM SAMPLE."v_test_pers"
 
TERADATA: trqacol- No casting. Raw row size=39, Casted size=39, CAST_OVERHEAD_MAXPERCENT=20%
 
TERADATA_7: Prepared: on connection 2
SELECT "SUB_ID","SSN_NBR" FROM SAMPLE."v_test_pers"
 
 
TERADATA_8: Executed: on connection 2
SELECT "SUB_ID","SSN_NBR" FROM SAMPLE."v_test_pers"  
 
TERADATA: trget - rows to fetch: 2
TERADATA: trforc: COMMIT WORK
 
DBMS_TIMER: summary statistics
DBMS_TIMER: total SQL execution seconds were: 0
DBMS_TIMER: total SQL prepare seconds were: 0
DBMS_TIMER: dbiopen/dbiclose timespan was 1.

[BrunoMueller]

Hi

Find below a complete example, although using Oracle, it should work the same for other DBMS. It seems, you do need an ORDER BY to pass down the function to the DBMS, without the ORDER BY the SAS function gets called.

proc sql;
  connect to oracle (
    path=xe user=educ password=educ
    );
  execute (
    CREATE OR REPLACE FUNCTION FROMSAS 
      (
      PARAM1 IN VARCHAR2 
      , PARAM2 IN VARCHAR2 
      , PARAM3 IN NUMBER 
      , PARAM4 IN NUMBER 
      , PARAM5 IN NUMBER 
      ) RETURN VARCHAR2 AS 
      BEGIN
      RETURN param1 || param2 || ':' || param3 || param4 || param5;
  END FROMSAS;
  ) by oracle;
quit;

proc fcmp outlib=work.funcs.trial;
  function fromsas(col1 $,Col2 $,Col3,Col4,Col5) $ 400;
    return ( catx("_", col1, col2, col3, col4, col5) );
  endsub;
run;

options cmplib=work.funcs;

/* Test the new function in sas*/
data _null_;
  x = dummysas("a", "b", 1, 2, 3);
  put x=;
run;

proc sql;
  create table someTest as
    select
      name
      , sex
      , age
      , height
      , weight
      , fromsas(name, sex, age, height, weight) as fromsas
    from
      sashelp.class
  ;
quit;

/*add function to sql dictionnary*/
data work.newdbmsfunc;
  SASFUNCNAME = "FROMSAS";
  SASFUNCNAMELEN = length(sasfuncname);
  DBMSFUNCNAME = "FROMSAS";
  DBMSFUNCNAMELEN = length(dbmsfuncname);
  FUNCTION_CATEGORY = "SCALAR";
  FUNC_USAGE_CONTEXT = "SELECT_LIST";
  FUNCTION_RETURNTYP = "CHAR";
  FUNCTION_NUM_ARGS = 0;
  CONVERT_ARGS = 0;
  ENGINEINDEX = 0;
  output;
run;

/* Test the UDF function in Oracle */
libname xora oracle path=xe user=educ password=educ
  SQL_FUNCTIONS="EXTERNAL_APPEND=work.newdbmsfunc"
  SQL_FUNCTIONS_COPY= saslog
;
options
  sastrace =',,,d'
  sastraceloc=saslog
  nostsuffix
;

proc sql;
  drop table xora.classtest;
quit;

proc append
  base=xora.classtest
  data=sashelp.class
;
run;

proc sql;
  create table someTest_dbms as
    select
      name
      , sex
      , age
      , FROMSAS(name, sex, age, height, weight) as fromsas
    from
      xora.classtest
    order by
      name
  ;
quit;

Good luck

Bruno

[pchegoor · Accepted solution]

@BrunoMueller       Thank  you for not giving up on this and showing me the right direction. 

   Your  ORDER BY suggestion worked!  The function returned the correct value when

    used in an IMPLICIT Pass Through Query.

    Infact  i had approached SAS Tech Support with same question and i was told that it is not possible to use the

     UDF in an IMPLICIT Pass through   Query but that it can only be used in an  EXPLICIT  Pass through Query. 

     This ONLY proves the SAS Language has many hidden features which not many are aware of

                              This has been a Great discussion .  Learnt  some new features .  Thank  you once again.

                               Below  is  my  SAS Code incase someone  is interested in the same . 

                               Typically  it has the same logic as suggested by you  above .

/*****************************************************************************************
  The Purpose of this Program is to utilize a Teradata UDF (User Defined Function)
  in an Implicit PROC SQL  Pass through Query in SAS.

  Assumption :  A  Teradata UDF already has been created with the name PTY_DECRYPT
                inside the Database TEST. The function has 5 arguments with the 1st
                and 2nd being of type VARCHAR and the rest being INTEGER. The Function also
                returns a string of type VARCHAR  and length 400.The purpose of the Function
                is to decrypt the value of column (SSN_NBR) in a Teradata view (V_TEST_PERS)
                located in a Database called SAMPLE

                Eg. Function Usage is as follows in an Explicit Sql  SELECT  statement:

                  TEST.PTY_DECRYPT(SSN_NBR,'T_ssn_test',400,0,0 )  as SSN_NBR_Decrypt 

********************************************************************************************/




/*This  Option  Statement  is necessary to prevent WARNING : Function <Function name> was defined in a previous package .......*/

Options cmplib=_NULL_;

/*Define a Dummy Function called dummysas using PROC FCMP having same Number and Type of Arguments as the Teradata UDF 
  The function also returns a string of length 50  similar to Teradata UDF. Note: Though the Teradata UDF returns a string 
  of length 400 characters you can use a value less than this or specify no value for this */

proc fcmp outlib=WORK.funcs.trial;
  function dummysas(col1 $,Col2 $,Col3,Col4,Col5) $ 50;
    return ('testvalue');
  endsub;
run;



/*Specify  the  SAS Dataset which contains the above compiled Function */

options cmplib=work.funcs;

/*  Test  the  Function  */

data A;
  x=dummysas('1','2',0,0,0);
  put x=;
run;

/* Create a  Dataset  with the above created Function and Teradata UDF  properties 
  to be added to the existing function list in the SQL dictionary */

/*For More Information see : http://goo.gl/OyNcyN */

data work.newfunc;
  SASFUNCNAME = "dummysas";    /*Specify correct name of the SAS  function above */
  SASFUNCNAMELEN = length(SASFUNCNAME); /*Specify correct length of the SAS function name */
  DBMSFUNCNAME = "TEST.PTY_DECRYPT"; /*Specify correct name of the Teradara UDF*/
  DBMSFUNCNAMELEN = length(DBMSFUNCNAME);/*Specify correct length of the Teradara UDF */
  FUNCTION_CATEGORY = "SCALAR";    /* Specify SCALAR  category */
  FUNC_USAGE_CONTEXT = "SELECT_LIST"; /* Specify Function Usage Context ie SELECT_LIST in this case */
  FUNCTION_RETURNTYP = "VARCHAR"; /*Specify CHAR or VARCHAR as Function Return  Type*/
  FUNCTION_NUM_ARGS = 0; /*Specify default value of 0 */
  CONVERT_ARGS = 0;  /*Specify default value of 0 */
  ENGINEINDEX = 0;  /*Specify default value of 0 */
  output;
run;

/*  Define  Teradata  Connection  using a Libname  Statement and add Options SQL_FUNCTIONS and SQL_FUNCTIONS_COPY */

Libname Td Teradata  User=test123 pwd=test123 server=TD_EDW database=SAMPLE  SQL_FUNCTIONS="EXTERNAL_APPEND=work.newfunc"
  SQL_FUNCTIONS_COPY= saslog ;


/* Display in the SAS Log information about where  the Query Processing occurs   */

  Options  debug=DBMS_TIMERS sastrace=',,,d'
sastraceloc=saslog no$stsuffix fullstimer ;


/*Use  the function dummysas  in the Implicit  Query */
/*Note :  The ORDER BY  Clause is necessary for the Function to be pushed to Database for Processing */

  Proc sql;
Create  table Temp as
select
sub_id , 
SSN_NBR,
dummysas(SSN_NBR,'T_ssn_test',50,0,0) as SSN_NBR_Decrypt 
from Td.V_TEST_PERS 
order by 1;
Quit;

[BrunoMueller]

Hi

Glad it finally worked, and thanks for sharing the code you have used.

Bruno