topic Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru in SAS Programming

Using a Teradata UDF in SAS Implicit Sql Pass Thru

pchegoor — Tue, 26 Apr 2016 17:23:17 GMT

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.

Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru

BrunoMueller — Tue, 26 Apr 2016 19:21:51 GMT

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

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:

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
add the function information to a SAS data set
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

Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru

pchegoor — Wed, 27 Apr 2016 19:54:59 GMT

@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 ?

Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru

BrunoMueller — Wed, 27 Apr 2016 19:26:35 GMT

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

Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru

BrunoMueller — Wed, 27 Apr 2016 19:35:04 GMT

Just noticed the value for

SASFUNCNAMELEN = 9;

should by

SASFUNCNAMELEN = 8;

maybe this helps

Bruno

Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru

BrunoMueller — Wed, 27 Apr 2016 20:19:49 GMT

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

Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru

pchegoor — Wed, 27 Apr 2016 21:05:33 GMT

@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&colon; 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&colon; trget - rows to fetch: 2
TERADATA&colon; 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.

Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru

pchegoor — Thu, 28 Apr 2016 06:41:07 GMT

@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;

Re: Using a Teradata UDF in SAS Implicit Sql Pass Thru

BrunoMueller — Thu, 28 Apr 2016 15:39:54 GMT

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

Bruno