topic Re: How to add single quote to a macro variable within a macro in SAS Programming

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

LL5 — Wed, 02 Dec 2020 22:23:23 GMT

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"

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

ballardw — Wed, 02 Dec 2020 22:43:29 GMT

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.

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

Tom — Wed, 02 Dec 2020 22:54:30 GMT

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()

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

PaigeMiller — Wed, 02 Dec 2020 22:48:40 GMT

%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;

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

LL5 — Wed, 02 Dec 2020 23:03:00 GMT

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>

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

LL5 — Wed, 02 Dec 2020 23:07:18 GMT

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.

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

PaigeMiller — Wed, 02 Dec 2020 23:08:42 GMT

@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.

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

LL5 — Wed, 02 Dec 2020 23:12:29 GMT

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'

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

SASKiwi — Wed, 02 Dec 2020 23:41:21 GMT

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);

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

LL5 — Thu, 03 Dec 2020 00:25:07 GMT

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

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

SASKiwi — Thu, 03 Dec 2020 02:12:30 GMT

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

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

Ksharp — Thu, 03 Dec 2020 12:48:16 GMT

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

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

Tom — Thu, 03 Dec 2020 14:27:24 GMT

@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.

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

Aniruddhaanu — Tue, 12 Jul 2022 08:54:01 GMT

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 ?

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

Ksharp — Tue, 12 Jul 2022 12:03:43 GMT

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 .