Hi, I'm trying to create a User-Defined Action in CASL that makes use of the FEDSQL action. The issue I have is with resolving the parameter passed to the action. This needs to be in single quotes within the definition - once resolved. The code below sets up a dummy data set, and then creates an action set with 2 actions. They do the same thing but the second has a parameter to allow passing of a string to the query. Its the second one that fails and I cant find the right way to code the quoting of the parameter in the SQL query. Or maybe its just not possible with the quoting within double-quotes format of defining the fedSQL.execDirect definition. /*start a CAS session called mySESS */
cas mySESS;
/* create a libref (MYLIB) using the CAS engine */
libname MYLIB cas;
/* create a dataset in MYLIB library (the active CASLIB being CASUSER)*/
Data MYLIB.aaa;
length userName varchar(10);
userName = 'aaa';TelNum = 111111;output;
userName = 'bbb';TelNum = 222222;output;
run;
caslib _all_ assign;
/*create an action set called tblMakers*/
/* this includes 2 actions using fedSQL to create a table */
proc cas;
builtins.defineActionSet /
name="tblMakers"
label="make some new tables"
actions={
{
name="tableCreate"
desc="create table in myLib"
definition="
fedSQL.execDirect / query='create table casuser.bbb {options replace=true} as
select userName
from CASUSER.aaa
where userName = ''bbb'' ';
"
}
{
name="tableCreateParam"
desc="create table in myLib with Param"
parms={
{name="usernameQ" type="STRING" required=True}
}
definition="
fedSQL.execDirect / query='create table casuser.ccc {options replace=true} as
select userName
from CASUSER.aaa
where userName = usernameQ ';
"
}
};
run;
/* run the tableCreate Action - this will work*/
proc cas;
tblMakers.tableCreate result=tbl;
run;
/* run the tableCreate Action - this will fail*/
proc cas;
tblMakers.tableCreateParam / usernameQ="bbb";
run; I know alternatives to fedSQL can be used ...and maybe we'll need to use them but SQL is strong here so is the preferred choice if available. Thankyou for looking.
... View more
