topic Re: Replace multiple quotes with single quote in a macro variable in SAS Programming

Replace multiple quotes with single quote in a macro variable

David_Billa — Tue, 20 Apr 2021 08:48:05 GMT

I've one macro macro variable which is resolves to ''''''0001'''''' as you see in log below. I'm not using any single quote or double quotes while calling that macro variable. Can someone guide me to tackle this siutation by replacing multiple quotes with single quote? If I do so, then I believe this error can be elimninated.

ERROR: All variables in array list must be the same type, i.e., all numeric or character.
MPRINT(TEST):   if scan(line, 5, " ", 'm') not in (''''''0001'''''') then error = error * 45;
NOTE: Line generated by the macro function "SYSFUNC".
4157       ''''''0001''''''

Re: Replace multiple quotes with single quote in a macro variable

yabwon — Tue, 20 Apr 2021 08:59:06 GMT

How the macro variable is created?

Re: Replace multiple quotes with single quote in a macro variable

Patrick — Tue, 20 Apr 2021 09:03:57 GMT

If possible resolve the issue at the source. How do you create and populate this macro var?

But here you go

%let var='''''''abc''''''';
%let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
%put &=var;

Re: Replace multiple quotes with single quote in a macro variable

David_Billa — Tue, 20 Apr 2021 09:39:44 GMT

@Patrick @yabwon I'm creating the macro variable as you see below.

%let id=0013;
%let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),',')); 
%put &id.;

and I'm calling it like &id.

Re: Replace multiple quotes with single quote in a macro variable

yabwon — Tue, 20 Apr 2021 09:43:37 GMT

If you run it like that how do you get those multiple quotes?

My log is:

1    %let id=0013;
2    %let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),','));
3    %put &id.;
'0013'
4    %let id=0013,0017;
5    %let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),','));
6    %put &id.;
'0013','0017'
7    %let id=0013,0017,0042;
8    %let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),','));
9    %put &id.;
'0013','0017','0042'

Bart

Re: Replace multiple quotes with single quote in a macro variable

Kurt_Bremser — Tue, 20 Apr 2021 09:45:59 GMT

You are doing something in the macro that adds the quotes; they don't appear magically out of nothing:

%let id=0013;
%let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),',')); 
%put &id.;

%macro check(in);
data check;
x1 = &id.;
run;
%mend;

%check(&id.)

BTW, in about 99% of cases, quotes in macro variables are not needed and cause only unnecessary work. I suggest you review your code design.

Re: Replace multiple quotes with single quote in a macro variable

David_Billa — Tue, 20 Apr 2021 09:46:07 GMT

@Patrick I tried your code with different values and with different number of quotes and the result is,

26         %let var='''''''1234''''''';
27         %let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
28         %put &=var;
VAR=1234
29         
30         
31         %let var='''''0401''''';
32         %let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
33         %put &=var;
VAR=0401
34         
35         
36         %let var=''5678'';
                    __
                    49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

37         %let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
38         %put &=var;
VAR=5678
39         
40         %let var='1111';
41         %let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
42         %put &=var;
VAR=1111

Program which I ran is,

%let var='''''''1234''''''';
%let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
%put &=var;


%let var='''''0401''''';
%let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
%put &=var;


%let var=''5678'';
%let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
%put &=var;

%let var='1111';
%let var=%sysfunc(prxchange(s/%nrbquote(')+//,-1,%nrbquote(&var)));
%put &=var;

Desired result is,

VAR='1234'
VAR='0401'
VAR='5678'
VAR='1111'

Re: Replace multiple quotes with single quote in a macro variable

Patrick — Tue, 20 Apr 2021 10:11:31 GMT

Use a data step instead. That's easier to read and "safer". ....but much better fix the issue at the source so that you never end-up with that many quotes.

%let var=%nrbquote(''5678'');
data _null_;
  var=symget("var");
  var=compress(var,"'");
  call symputx('var',cats("'",var,"'"));
  stop;
run;
%put %nrbquote(&var);

Re: Replace multiple quotes with single quote in a macro variable

ballardw — Tue, 20 Apr 2021 14:37:30 GMT

@David_Billa wrote:

@Patrick @yabwon I'm creating the macro variable as you see below.
%let id=0013;
%let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),',')); 
%put &id.;
and I'm calling it like &id.

That places one set of single quotes around the value. What are you doing that manages to place multiple sets? Are you calling the

%let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),','));

portion multiple times without resetting ID to the base value?

Example:

1    %let id=0013;
2    %let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),','));
3    %put &id.;
'0013'
4    %let id=0013;
5    %let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),','));
6    %put First time: &id.;
First time: '0013'
7
8    %let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),','));
NOTE: Line generated by the macro function "SYSFUNC".
1    ''0013''
     --
     49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
             release.  Inserting white space between a quoted string and the succeeding
             identifier is recommended.

9    %put Second time: &id.;
NOTE: Line generated by the macro variable "ID".
1     ''0013''
      --
      49
Second time: ''0013''
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
             release.  Inserting white space between a quoted string and the succeeding
             identifier is recommended.

10
11   %let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),','));
12   %put Third time: &id.;
Third time: '''0013'''

So it appears that you have a loop that is calling your %let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),',')); multiple times.

Don't do that and you extra quotes likely go away.

Or reset the initial value.

Re: Replace multiple quotes with single quote in a macro variable

Tom — Tue, 20 Apr 2021 17:55:33 GMT

@David_Billa wrote:

@Patrick @yabwon I'm creating the macro variable as you see below.
%let id=0013;
%let id=%sysfunc(tranwrd(%bquote('&id.'),%str(,),',')); 
%put &id.;
and I'm calling it like &id.

First thing to do is not overwrite your input macro variable with the modified value.

%let list=0013,0014;
%let qlist=%sysfunc(tranwrd(%bquote('&list'),%str(,),','));

If you really feel a need to overwrite the unquoted variable with the quoted version the first remove any quotes.

2334  %let id='0013';
2335  %let id=%sysfunc(tranwrd(%bquote('%qsysfunc(dequote(&id))'),%str(,),','));
2336  %put &=id;
ID='0013'

Now you won't end up with strings like '''0013'''.