Don't quote input in custom transformation

[Criptic]

Hello,

I created a custom transformation inside of DI Studio and I'm using the input mask. When a string is entered into one of these fields %let var1 = %nrquote(input of user) is automatically entered but the coding below doesn't work with a quoted string instead it needs the unquoted one - safety concerns are not an issue here - is there an option which suppress %nrquote function or do I have to %let var1 = %unquote(&var1.)?

Kind regards

Criptic

[Tom]

You will probably need to add your own logic to remove the macro quoting. I don't think DI has the smarts to know when it is safe to NOT add the macro quoting.

If the field is really being completed by users then you should also add some logic to check the value before blindly using it.

Perhaps by getting out of macro world and into SAS code instead.  Although that might be harder in DI.

For example if the value is supposed to be string that is assigned to a character variable you can use the SYMGET() function

myvar = sysget('myPrompt');

instead of expanding the macro variable.

myvar ="&myPrompt";

to prevent strings with unbalanced quotes from breaking your code.

Or if the value is intended to be used as a variable name then use NVALID() or NLITERAL() function to test it or fix it up before using it.

[ErikLund_Jensen]

Hi @Tom & @Criptic 

What sort of input do you expect? - the %nrquote function does not add quotes, it only masks the string, so the macro compiler ignores & and % in the string and does not try to resolve variables.  But using these characters in the string will give a warning in DI Studio before execution of the supplied code. If the string is just plain text without & or %, the %nrquote function does not make any difference.

I made a small example transformation, and here is a snippet of the log:

110        
111        %let myinput = %nrquote(This is ' a &text);
WARNING: Apparent symbolic reference TEXT not resolved.
112        
113        /* Code supplied to User Transformation */
114        
115        %put &=myinput;
MYINPUT=This is ' a &text
116        
117        data _null_;
118        	a = "&myinput";
119        	put a=;
120        run;

a=This is ' a &text

Execution of the DI Studio generated code %let myinput = %nrquote((This is a &text); gives a warning, but thanks to the masking function &text is not resolved, so &myinput contains the string supplied.

It can be used in macro code without problem, as you see in the line written by %put &=myinput;

And it can be used in a data step by adding quotes where you refer to it, just like "normal" macro variables, so there is no need for symget functions, as you can see working in the data step.

And you don't need any special precautions to cope with unbalanced quotes, as you see in the example.

This also shows that it is not hard to use data steps in a DI Studio User Transformation. The User Transformation is a container for SAS code, and any program that works in a SAS Display Manager session will also work in a User Transformation. The code can contain any numbers of Proc- and Data steps and macro code, so a Data _NULL_ step to evaluate and maniputate user input can safely be added as a first step in the code.

[Criptic]

Thank you @Tom  and @ErikLund_Jensen  for your answers:

The input I expect is the name of a column, a lib name, a table name and a cube name. The Transformation auto generates MDX for the cube based on the column names and values. The thing is I have a proc sql in there in the form of:

proc sql;
create table work.step_1 as
select distinct &col.
from &lib..&table.;
quit;

Now if the input gets put through the %nrquote function it won't work anymore. So what I'm doing now is I use %unquote and then it works again. I have no idea why it doesn't work with %nrquote but it doesn't. Following I created an example coding which doesn't run with the macro variables which where run through %nrquote:

%let lib = sashelp;
%let table = class;
%let col = name;

proc sql;
create table work.aaa_&table. as
select &col. from &lib..&table.;
quit;


%let lib = %nrquote(sashelp);
%let table = %nrquote(class);
%let col = %nrquote(name);

proc sql;
create table work.aaa_&table. as
select &col. from &lib..&table.;
quit;

If you unquote the value of the macro variable it works again.

Kind Regards

Criptic

[Patrick · Accepted solution]

%unquote() is the way to go in my experience.

[Criptic]

Yeah seems like my only solution, was just hoping somebody knew an option 😄

[Patrick]

@Tom 

DIS is for development of ETL processes and they are always run in batch in a production environment without direct user input. The prompts are used for data driven parameter passing only.

Even when running jobs out of the DIS client during development you'll never get a prompt for user input as you could using other clients like EG.