variables within quotes

[markhh]

In the past, the quote encapsulation rules were simple: single quotes blocked variable expansion, double quotes did not. Now, I can't figure out what is the correct method.

For example, I have a script where I want to pass values on the command line using -sysparm "val1 val2 val3..."

I read them in my script:

%let var1=%scan(&sysparm,1)

%let var2=%scan(&sysparm2)

etc...

I then use a variable in a sql statement:

proc sql

execute(

select * from table where column=&var1;

)

If I use either single or double quotes, I get an error that column val1 does not exist. (val1 is the value of variable var1). The behavior is inconsistent, it depends on the context where it occurs (for example, proc sql vs data vs a %macro definition)

In order to get it to work, I have to do this

proc sql

execute(

select * from table where column=%str(%'&var1%');

)

This is with sas 9.4. With previous versions this wasn't necessary and behavior was consistent. 

Sometimes quotes are interpreted as a delimiter, sometimes it is included in the variable value. sometimes it blocks variable expansion, sometimes not. Where is the behavior of variables and quotes defined? When are quotes required, when are they not?

[maguiremq]

In the past with pass-through, which you are showing, I have done this to variables:

%str(%')<value>%str(%')

Not sure if that will resolve your issue. I think it gets difficult because you mention the command line and what not.

And I primarily use Oracle with passthrough, which requires single quotes. If it's in a macro, the single quotes will cause issues.

[Quentin]

The rules haven't changed.  As you say, single quotes block macro variable resolution, double quotes do not.

I see two potential issues. 

I think if you do: -sysparm "val1 val2 val3..." the macro variable sysparm will have the quotes in the value. Is that right? I can't remember.

%SCAN will not treat  treat a quote as a delimiter unless you tell it to.  You can tell it to treat both a double quote and space as delimiters like:

%let var1= %scan(&sysparm,1,%str(%" )) ; 
%let var2= %scan(&sysparm,2,%str(%" )) ; 

The other issue is it looks like you are sending the SELECT statement of to a RDBMS to execute.  Some databases will accept text values in single or double quotes, some only like single quotes.  So if you are working with a database that only likes single quotes, then your approach of

select * from table where column=%str(%'&var1%');

 is a reasonable way to do it.  But the need for that is driven by the external database, not SAS.

(it bugs me a little that your macro variable named var1 actually holds a value, but that is besides the point)

[ballardw]

---

@Quentin wrote:

(it bugs me a little that your macro variable named var1 actually holds a value, but that is besides the point)

---

I was having a hard time with Var and Val as well but the code was using a variable Column so was really confused what was supposed to be where (thanking that there wasn't a Where clause yet). Maybe an example of trying to anonymize too much.

[markhh]

The values I'm passing are strings, not numerics, the database is Sybase.

If I specify WHERE column=&var1, it is being interpreted as a numeric and I get an error to the effect that I have a type mismatch

WHERE column='&var1' blocks the variable expansion

WHERE column="&var1" results in the error 'Column (value of var1) doesn't exist.

I'm beginning to suspect the problem is a change in Sybase, not SAS. Sybase may have changed to only recognize single quotes as a string delimiter.

[Tom]

I don't know anything about SYBASE syntax, but I would suspect that the change is probable from using implicit SQL generation to explicit passthru SQL generation.

If you let SAS generate the SYBASE SQL for you then you can use SAS syntax for your character constants.

libname mylib sybase ...... ;
data want;
  set mylib.mytable;
  where column="val1";
run;

But if you write the SYBASE SQL yourself you need to follow their rules.

proc sql;
connect using mylib;
create table want as select * from connection to mylib
 (select * from mytable where column='val1')
;
quit;

[Tom]

Are the variables in the passthru database NUMERIC or CHARACTER?

If they are character you want to generate code like:

where column='VAL1'

Since most external databases required single quotes around string literals.  Double quotes would be a reference to a variable named VAL1.

In which case it is probably going to be easiest to just put the single quotes in the SYSPARM value:

-sysparm "'val1' 'val2' 'val3'"

So that your macro code can simply be (always be specific about what characters %SCAN() should use as delimiters):

%let var1=%scan(&sysparm,1,%str( ));
....
where column=&var1 
...

If you do want to convert a space (or even some other delimiter) delimited list into a quoted list instead use something like this 25 year old macro: https://github.com/sasutils/macros/blob/master/qlist.sas 

Then your code will be as simple as:

-sysparm "val1 val2 val3"

And

where column in %qlist(&sysparm)

[Patrick]

SAS is highly backward compatible and if you haven't changed anything in your code and command for batch execution then everything should still work after an upgrade.

Instead of passing parameter values via -sysparm you could also use -initstmt and directly create your set of macro variables. 

..... -initstmt '%let var1=10; %let var2=abc;'

You then can use these macro variables in code as below

where double_var=&var1 and varchar_var=%tslit(&var2)

The SAS supplied macro %tslit() will embed the resolved macro variable into single quotes.

And to not use "mystically" in your code macro variables that got nowhere created you could also add something like below at the top of your script.

/* ensure expected batch call created macro vars exist */
%if not %symexist(var1) %then %do; %let var1=-999999; %end;
%if not %symexist(var2) %then %do; %let var2=*******; %end;

 If you just want to ensure the macro vars exist but don't need to set some default value then you can also simply use: %global var1 var2;

[Tom · Accepted solution]

Note that the simplest way to add single quotes is to use the %BQUOTE() macro function as that will allow single quotes in its input and yet still evaluate the macro triggers.

Use %QSCAN() incase the string pulled from SYSPARM as macro triggers itself.

Once you have the single quotes around the value you can safely remove the macro quoting.

%let var1=%unquote(%bquote('%qscan(&sysparm,1,%str( ))'));

If it is possible the "value" has single quotes in it that you will probably want to use the QUOTE() function wth the optional second argument of a single quote instead so that any embedded single quotes are properly handled.

%let var1=%sysfunc(quote(%qscan(&sysparm,1,%str( )),%str(%')));