How to add single quote to a macro variable within a macro

[LL5]

Hi all, I am trying to add a single quote to a macro variable (&city) within a macro (%y).

After I ran the below code, &z returns a value with double quote which is "nyc", my goal is to get the value with single quote which is 'nyc'.

Any advices are gladly appreciated.

%let city = nyc;
%macro y;
%let z= %sysfunc(quote(&city,"'"));
%put &z;
%mend;
%y;

log as below:

GOPTIONS ACCESSIBLE;
29 %let city = nyc;
30 %macro y;
31 %let z= %sysfunc(quote(&city,"'"));
32 %put &z;
33 %mend;
34 %y;
"nyc"

[ballardw]

What is so critical about single quote?

Attaching quotes to macro variables except for very specific reasons is often a poor idea and can add a lot of complexity as may find that you need to use the masking functions like %str %nrstr or %superq to keep other code from misbehaving.

[LL5]

The reason is this variable will be used as a filter condition via ODBC connection with MS SQL server (see below). SQL server doesn't recognized "nyc" or nyc, it only recognizes value within single quote like 'nyc'.

I could create another macro variable using call symputx within data step or simply using %let, but I just wonder if there is any way to add single quote to this macro variable within a macro.  

%MACRO inv(table);
PROC SQL;
  CONNECT TO odbc(id=&id pwd=&pwd dsn=inv); 
  EXECUTE (delete from inventory.&TABLE where city = &city ) by odbc;
  disconnect from odbc;
QUIT;
<more code here>

%MEND; 

%inv(table1);
%inv(table2);
%inv(table3);
%inv(table4);
<more tables>

[PaigeMiller]

---

@LL5 wrote:

The reason is this variable will be used as a filter condition via ODBC connection with MS SQL server (see below). SQL server doesn't recognized "nyc" or nyc, it only recognizes value within single quote like 'nyc'.

---

Actually, that's the only reason I can think of why you might want a macro variable value surrounded by quotes.

... but I just wonder if there is any way to add single quote to this macro variable within a macro.

@Tom has provided the answer.

[Tom]

To the macro processor everything is a string. So there is no need to add quotes around string literals to show it you didn't mean to type a variable name or keyword.  So you gave the QUOTE function the three character string "'" instead of the one character string '.

%let z= %sysfunc(quote(&city,%str(%')));

You probably also want to make sure the value of CITY doesn't have unbalanced quotes or macro triggers.  Try adding %SUPERQ().

%let z= %sysfunc(quote(%superq(city),%str(%')));

Or use utility macro like %squote() 

[LL5]

awesome, this works well

%let city = nyc;
%macro y;
%let z= %sysfunc(quote(&city,"'"));
%let zz= %sysfunc(quote(&city,%str(%')));
%put &z &zz;
%mend;
%y;

log below 

29 %let city = nyc;
30 %macro y;
31 %let z= %sysfunc(quote(&city,"'"));
32 %let zz= %sysfunc(quote(&city,%str(%')));
33 %put &z &zz;
34 %mend;
35 %y;
"nyc" 'nyc'

[PaigeMiller]

%let city = nyc;
data _null_;
	call symputx('z',quote("&city","'"));
run;
%put &=z;

I add that the values of macro variables almost never need to be surrounded by either single or double quotes, as this makes the coding much more difficult. I strongly urge you to avoid this.

Much simpler code works in almost every situation I can possibly think of:

%let z=nyc;

[LL5]

Thanks. I could create a macro variable with single quote in a separate step, but I just want to explore the possibility of adding single quote for a macro variable within a macro. 

[SASKiwi · Accepted solution]

Personally I find it cleaner and more flexible to not wrap macro variable values in quotes except where it is used:

%MACRO inv(table,city);
PROC SQL;
  CONNECT TO odbc(id=&id pwd=&pwd dsn=inv); 
  EXECUTE (delete from inventory.&TABLE where city = %str(%')&city%str(%') ) by odbc;
  disconnect from odbc;
QUIT;
<more code here>

%MEND;

%inv(Mytable, NYC); 

[LL5]

Great. This approach works really well. Thanks for the advice.

[SASKiwi]

@LL5  - Great, glad you like it. You are also being consistent with the way macro values in double quotes work as well. 

[Tom]

---

@SASKiwi wrote:

@LL5  - Great, glad you like it. You are also being consistent with the way macro values in double quotes work as well. 

---

Depends on how much trust you have that the macro variable values do not contain quotes already.  That is the added value that using the QUOTE function provides.  Note that if you are passing the string to some foreign system then you need to know whether that system follows SAS's rules for nested quotes or uses some other syntax. For example the Unix command shell wants you use the \ to escape embedded quotes instead of doubling them.

[Ksharp]

%let city = nyc;
%macro y;
%let z= %bquote(' &city ') ;
%let zz=%bquote(' &city ') ;
%put &z &zz;
%mend;
%y

[Aniruddhaanu]

Data _null_
Call symput ('Month',put(intnx('month',today(),-2,'B'),monname.));
%put &month1. "22
While resolving macro variable month1 i want "22 ( single quite and 22 ) at the end of macro variable how i can add ?

[Ksharp]

Plz start a brand-new session, this topic is almost two years ago .
And you could let other sas experts know this question and get better answer .