Creating User-Defined Actions in SAS^© Viya^™ allows for encapsulating complex logic to make it reusable. However, a technical challenge often arises when attempting to embed FedSQL code within these definitions: handling quoting when passing parameters (such as character strings) in the SQL query.

This article explores methods to solve this problem, ranging from simple concatenation to the dynamic construction of WHERE clauses.

The Problem: The "Quote Soup"

The objective is simple: create a CAS action that takes a username as a parameter and creates a table filtered on that name via fedSQL.execDirect.

The challenge lies in the syntax. The action definition itself is a character string (often in double quotes). The FedSQL query is a string within that definition (often in single quotes). Finally, the literal value in the SQL (the username) must be enclosed in single quotes.

A naive attempt often fails because the parameter is not resolved correctly within the literal string of the query.

Solution 1: Clean Concatenation

The most robust method for inserting a CASL variable (your action's parameter) into the FedSQL query string is to use the concatenation operator (||) inside the definition.

You need to "exit" the SQL string, concatenate the variable, then "re-enter" the string, all while ensuring that the single quotes required for SQL (') are present.

Code Example

Here's how to define the action so that it accepts a usernameQ parameter and inserts it cleanly:

Solution 2: Dynamic Logic and Optional WHERE Clause

A more advanced approach is to use the power of the CASL language inside the action definition (definition=...). Since the definition contains executable CASL code, you can use IF/ELSE conditions to build your SQL query dynamically before executing it.

This allows, for example, to pass a complete WHERE clause or to leave it empty.

1	PROC CAS;
2	BUILTINS.defineActionSet /
3	name="tblMakers"
4	actions={
5	{
6	name="tableCreateParam"
7	/* Définition des paramètres attendus par l'action */
8	parms={{name="tblName" type="STRING" required=True}
9	{name="usernameQ" type="STRING" required=True}}
10
11	/* La définition utilise la concaténation pour insérer les variables */
12	definition=
13	"fedSQL.execDirect / query='create TABLE casuser.'\|\|strip(tblName)\|\|' {options replace=true} as
14	select userName, TelNum
15	from CASUSER.have
16	where userName='''\|\|strip(usernameQ)\|\|''' ';"
17	}
18	};
19	RUN;

1	/* Astuce : gérer la longueur maximale des chaînes citées si nécessaire */
2	%let _sv_quotelenmax=%sysfunc(getoption(quotelenmax));
3	options noquotelenmax;
4
5	PROC CAS;
6	BUILTINS.defineActionSet /
7	name="tblMakers"
8	type="CASL"
9	actions={
10	{
11	name="tableCreateParam"
12	parms={ {name="tblName" type="STRING" required=True}
13	{name="whereClause" type="STRING" required=False}
14	}
15	definition=
16	"
17	/* Logique CASL interne : vérifier si le paramètre existe */
18	if exists('whereClause') then whereClause=catx(' ','where',whereClause);
19	else whereClause='';
20
21	/* Construction et exécution de la requête */
22	fedSQL.execDirect / query='create TABLE casuser.'\|\|tblName\|\|' {options replace=true} as
23	select userName, TelNum
24	from CASUSER.have '\|\|whereClause\|\|' ';
25	"
26	}
27	};
28	RUN;
29
30	options &_sv_quotelenmax;

1	PROC CAS;
2	/* Appel avec un filtre complexe */
3	tblMakers.tableCreateParam /
4	tblName="resultat1"
5	whereClause="userName='User 1' or TelNum = 333333";
6
7	/* Appel sans filtre (crée une copie complète) */
8	tblMakers.tableCreateParam /
9	tblName="resultat2";
10	RUN;

Passing Dynamic Parameters to FedSQL in a Custom Action

Nivel de dificultad

Publicado el : 24/05/2023

Consejo del experto

The Problem: The "Quote Soup"

Solution 1: Clean Concatenation

Code Example

Solution 2: Dynamic Logic and Optional WHERE Clause

Advanced Code Example

Aviso importante