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