topic Re: Is it possible to create user defined functions using the SQL query node of the Data Management in SAS Data Management

Is it possible to create user defined functions using the SQL query node of the Data Management Stud

smicha — Thu, 26 Apr 2018 07:42:41 GMT

Hello,

I've got a data job where I need to validate tax codes. I know I can do this by using the expression node after the sql query node. But for my specific needs, which I can't elaborate here, I need to create a function in the database that I use as source. So is it possible to use the create function statement in the sql query node.

PS: I don't have access to the actual database, only through Data Connection in the Data Management Studio

Thanks in advance.

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

SASKiwi — Fri, 27 Apr 2018 07:29:16 GMT

How about you explain what you mean by "validate tax codes" because if you can't explain what you want we can't possibly provide a good solution. If for security reasons you can't provide the real codes you want to validate, make up an imaginary example that accurately describes what you want to do.

For example, let's say you wanted to validate a 1 character variable called Gender so that it is only able to hold the values M for Male, F for Female or blank. Please explain what you want like this.

Also what is the database that holds the data you want to validate?

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

smicha — Fri, 27 Apr 2018 07:41:18 GMT

Hi again,

Thank you for your answer. In database management systems (Microsoft SQL Server) you can create your own functions. The thing that I am asking is not that you provide the solution for my task (validate tax codes), but if the Data Management Studio 's query node or SQL Execute Node can create functions in the database that my data are residing. DB Version is DB2.

Thanks in advance.

Spyros

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

SASKiwi — Fri, 27 Apr 2018 07:46:37 GMT

If you can write it in DB2's flavour of SQL, then you can run it in SAS using SQL PASSTHRU using the EXECUTE statement. I suspect that is what the SQL Execute Node does.

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

smicha — Thu, 10 May 2018 13:10:22 GMT

As it seems you can't create a function using the SQL Execute Node, I get error: QSYS.{username} IS AN UNDEFINED NAME. TABLE (-204). I haven't find any other solution.

Edit 10/05 : By "i haven't find any other solution" i mean that i haven't any other solution to create a UDF function into the database by using Data Management Alone. Sorry for the inconvenience.

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

SASKiwi — Wed, 02 May 2018 22:03:18 GMT

I suggest you open a track with SAS Tech Support on this issue. Also you could try this just using SQL PASSTHRU outside of SAS Data Management.

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

LinusH — Thu, 03 May 2018 04:42:58 GMT

I don't understand "PS: I don't have access to the actual database, only through Data Connection in the Data Management Studio".
You should be able to use the same credentials in your connection to logon to SQL Server Management Studio.

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

SASKiwi — Thu, 03 May 2018 21:21:31 GMT

@LinusH - the OP says he is using DB2, unless I'm misinterpreting his posts.

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

LinusH — Fri, 04 May 2018 06:25:35 GMT

@SASKiwi oh he mentioned SQL Server as an example were you can create functions - got me confused....

Re: Is it possible to create user defined functions using the SQL query node of the Data Management

VincentRejany — Mon, 07 May 2018 07:46:19 GMT

You can either use one user defined function in the SQL query node if it has been created within MS SQL Server, or create your proper user defined function to be used in the expression language node (EEL). For that last option, you only need to edit your UDF into one text file to be copied into the etc/udf folder of your dm studio or server install. Please find below fex examples:

--> mind that you can encapsulate functions but the one called would have to exist before the caller.

--> for udf/changed to be taken into you will have to restart dm studio or your server.

// Function:        nvl
// Description:   Replace null in parameter1 with value in parameter2
// Inputs:            Field to test
                                replacement value if null
// Output:           Field with value replacing null
function nvl return String
String nvlString
if isnull(parameter(1)) then nvlString=parameter(2) else nvlString=parameter(1)
return nvlString
end function

// Function:        rtrim
// Description:   Trim trailing characters in parameter2 from parameter1
// Inputs:            Field to trim
//                            character to remove
// Output:           Trimmed Field
function rtrim return String
String rString
String cString
if isnull(parameter(1)) or isnull(parameter(2)) then rString=null
else
begin
rString=parameter(1)
cString=parameter(2)
while right(rString,len(cstring))==cString
   rString=left(rString,len(rString)-len(cString))
end
return rString
end function

// Function:        ltrim
// Description:   Trim leading characters in parameter2 from parameter1
// Inputs:            Field to trim
//                            character to remove
// Output:           Trimmed Field
function ltrim return String
String rString
String cString
if isnull(parameter(1)) or isnull(parameter(2)) then rString=null
else
begin
rString=parameter(1)
cString=parameter(2)
while left(rString,len(cstring))==cString
   rString=right(rString,len(rString)-len(cString))
end
return rString
end function

//////////////////////////////////////////////////////////////////////////////////
//This function calculates ISIN check digits using the "Modulus 10 Double Add //
//Double" technique used in CUSIPs //
// http://en.wikipedia.org/wiki/International_Securities_Identifying_Number //

// Author: Vincent Rejany //
// Version: 1.0 //
// Creation date: 20130304 //
// Modificationdate: //
// //
//////////////////////////////////////////////////////////////////////////////////

//////////////////////////////////////////////////////////////////////////////////
// Function: udfCheckIsin //
// Description: //
// Input: 1 string //
// Output: 1 boolean //
//////////////////////////////////////////////////////////////////////////////////

Function udfCheckIsin return boolean

string ISINi
string ISINc
string ISINa
integer ISINd
integer ISINs
integer i
boolean udfCheckIsin

ISINi = parameter(1)
ISINs = 0
udfCheckIsin = false

if len(ISINi) != 12 then
udfCheckIsin = false
else
begin
//Remove check digit
ISINc = left(ISINi,11)

//Convert any letters to numbers
ISINc = replace(ISINc,'A','10',0) ISINc = replace(ISINc,'B','11',0) ISINc = replace(ISINc,'C','12',0)
ISINc = replace(ISINc,'D','13',0) ISINc = replace(ISINc,'E','14',0) ISINc = replace(ISINc,'F','15',0)
ISINc = replace(ISINc,'G','16',0) ISINc = replace(ISINc,'H','17',0) ISINc = replace(ISINc,'I','18',0)
ISINc = replace(ISINc,'J','19',0) ISINc = replace(ISINc,'K','20',0) ISINc = replace(ISINc,'L','21',0)
ISINc = replace(ISINc,'M','22',0) ISINc = replace(ISINc,'N','23',0) ISINc = replace(ISINc,'O','24',0)
ISINc = replace(ISINc,'P','25',0) ISINc = replace(ISINc,'Q','26',0) ISINc = replace(ISINc,'R','27',0)
ISINc = replace(ISINc,'S','28',0) ISINc = replace(ISINc,'T','29',0) ISINc = replace(ISINc,'U','30',0)
ISINc = replace(ISINc,'V','31',0) ISINc = replace(ISINc,'W','32',0) ISINc = replace(ISINc,'X','33',0)
ISINc = replace(ISINc,'Y','34',0) ISINc = replace(ISINc,'Z','35',0)

/*
//Fill Array from right to left
for i = ISINd to 1 step -1
begin
ISINa = ISINa & mid(ISINc, i, 1)
end
*/

ISINd = len(ISINc)
ISINa = ISINc

//Sum characters
for i = 1 to ISINd
begin
if i % 2 then
begin
if mid(ISINa,i) < 5 then
ISINs = ISINs + mid(ISINa,i,1)*2
else
ISINs = ISINs + mid(ISINa,i,1)*2-9
end
else
ISINs = ISINs + mid(ISINa,i,1)
end

//Check digit validation
if (10 - (ISINs % 10)) % 10 == right(ISINi,1)
udfCheckIsin = true

end

return udfCheckIsin

End Function