topic Re: proc cas User-defined Action definition parameters (FedSQL and quoting) in SAS Programming

proc cas User-defined Action definition parameters (FedSQL and quoting)

Brew — Fri, 15 Mar 2024 12:46:16 GMT

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.

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Mazi — Fri, 15 Mar 2024 16:26:00 GMT

Hi,

This is the only way I could get it to work...

Data casuser.aaa;
	length userName varchar(10);
	userName = 'aaa';TelNum = 111111;output;
	userName = 'bbb';TelNum = 222222;output;
    userName = 'ccc';TelNum = 222222;output;
    userName = 'ccc';TelNum = 222222;output;
run;


proc cas;
	query = "query ="||'"'||"create table casuser.ccc {options replace=true} as select userName from casuser.aaa where username ='"||'"||usernameq||"'||"'"||'"'||";  fedsql.execDirect / query = query;";
	builtins.defineActionSet / 
		name="tblMakers" 
 		label="make some new tables"
		actions={
			{
			name="tableCreateParam" 
			desc="create table in myLib with Param"
			parms={
			{name="usernameQ" type="STRING" required=True}
			} 
			definition=query
			}
		};
run;

/* run the tableCreate Action - this will fail*/
proc cas;
tblMakers.tableCreateParam / usernameQ="ccc";
run;

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Brew — Fri, 15 Mar 2024 19:58:20 GMT

Thanks Mazi,
That does work 🙂 ...and I cant work out another way, let alone a more elegant one.

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Mazi — Sat, 16 Mar 2024 05:05:29 GMT

I’ll try playing around with it more. If I can clean it up, I’ll send it through.

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Mazi — Sat, 16 Mar 2024 05:49:58 GMT

I tried looking at it again but couldn't improve it further.

This was a good question, learnt something new about CASL myself.

Thanks

Mazi

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Patrick — Sat, 16 Mar 2024 09:12:45 GMT

After quite a bit of trial and error it appears below actually works (tested on Stable 2024.01).

data casuser.have;
  length userName varchar(10);
  userName = 'User 1';TelNum = 111111;output;
  userName = 'User 2';TelNum = 222222;output;
run;

/*create an action set called tblMakers*/
proc cas;
  builtins.defineActionSet / 
    name="tblMakers" 
    actions={
      {
      name="tableCreateParam" 
      parms={{name="tblName" type="STRING" required=True}{name="usernameQ" type="STRING" required=True}} 
      definition=
        "fedSQL.execDirect / query='create table casuser.'||strip(tblName)||' {options replace=true} as
            select userName, TelNum
            from CASUSER.have
            where userName='''||strip(usernameQ)||''' ';"
      }
    };
run;

/* run the tableCreateParam Action */
proc cas;
  tblMakers.tableCreateParam / tblName="want" usernameQ="User 2";
  table.fetch / table={ caslib="casuser" name="want"};
run;

Btw: In Viya 4 you get already as part of startup a cas session CASAUTO and a caslib and cas libname CASUSER.

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Mazi — Sat, 16 Mar 2024 09:00:25 GMT

Thanks, Patric

I’ll have to test this in Viya 3.5 to see if it works.

I think I tried something similar and it failed for me in 3.5.

Also, how do you get your keywords highlighted in blue as if it were on a SAS editor?

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Patrick — Sat, 16 Mar 2024 09:03:07 GMT

There have been a lot of improvements and added functionality since 3.5.

The code I've posted earlier was tested on Viya 4 version Stable 2024.01

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Mazi — Sat, 16 Mar 2024 09:05:44 GMT

@Patrick, I can confirm that your solution works in Viya 3.5 as well!

Thanks for this.

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Patrick — Sun, 17 Mar 2024 09:11:32 GMT

@Mazi & @Brew

Now that I've made my next step in understanding how things work like that I can actually use actual program logic in the DEFINITION bit and that the scope of the parameters is local to the action, what about below version that allows for any where clause logic.

data casuser.have;
  length userName varchar(10);
  userName = 'User 1';TelNum = 111111;output;
  userName = 'User 2';TelNum = 222222;output;
  userName = 'User 3';TelNum = 333333;output;
run;

/*create an action set called tblMakers*/
%let _sv_quotelenmax=%sysfunc(getoption(quotelenmax));
options noquotelenmax;
proc cas;
  builtins.defineActionSet / 
    name="tblMakers" 
    type="CASL"
    actions={
      {
        name="tableCreateParam" 
        parms={ {name="tblName" type="STRING" required=True}
                {name="whereClause" type="STRING" required=False}
              } 
        definition=
          " if exists('whereClause') then whereClause=catx(' ','where',whereClause);
            else whereClause='';
            fedSQL.execDirect / query='create table casuser.'||tblName||' {options replace=true} as
              select userName, TelNum
              from CASUSER.have '||whereClause||' ';
          "
      }
    };
run;
options &_sv_quotelenmax;

/* run the tblMakers.tableCreateParam Action */
proc cas;
  tblMakers.tableCreateParam / tblName="want1" whereClause="userName='User 1' or TelNum = 333333";
  tblMakers.tableCreateParam / tblName="want2";
/*   table.fetch / table={ caslib="casuser" name="want1"}; */
run;

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Mazi — Sun, 17 Mar 2024 14:14:01 GMT

@patric, I actually like what you’ve done here.

I think it can be super useful when you want to create tables consisting of various subsets.

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Brew — Sun, 17 Mar 2024 21:18:56 GMT

That is good @Patrick

That really does open it up. Now we have a choice of implementing Actions where you pass in a value and the Action takes care of the rest, and more 'open' ones where you pass in the SQL (or parts of).

Really helpfull.

Thanks both for you input.

Re: proc cas User-defined Action definition parameters (FedSQL and quoting)

Patrick — Tue, 19 Mar 2024 01:19:42 GMT

@Patrick wrote:

....

Btw: In Viya 4 you get already as part of startup a cas session CASAUTO and a caslib and cas libname CASUSER.

Please note that above statement is not correct and you can't rely on CASAUTO and CASUSER to always exist after startup.

Having said that: If you can see cas engine libraries in the Studio library pane right from beginning then there must have been a CAS session created as part of site specific config for SAS startup.