Macro Variable Quoting

[Babloo]

@Tom @Kurt_Bremser  using HEX format seem to be a beautiful solution. I tried with Tom's code but it's not removing the leading and trailing spaces which I introduced in the macro variable. Am I missing something? Solution should handle any type of gibberish.

%let parameter=  Sales, Operations,   DataManaagement     ;

data _null_;
   length parameter $32767;
   parameter=symget('parameter');
   length char $1 ;
   do index=1 to length(parameter);
       char=char(parameter,index);
       if not (' ' < char <= '~') then put index= char= $hex2. ;
   end;
run;

data _null_;
  call symputx('parameter',cats("'",tranwrd(compress(symget('parameter'),'090A0C0DA0'x),',',"','"),"'"));
run;

%put &parameter;

Log:

How to interpret index and char value (index=7 char=20) in the log. 

2   %let parameter=  Sales, Operations,   DataManaagement     ;
83   
84   data _null_;
85      length parameter $32767;
86      parameter=symget('parameter');
87      length char $1 ;
88      do index=1 to length(parameter);
89          char=char(parameter,index);
90          if not (' ' < char <= '~') then put index= char= $hex2. ;
91      end;
92   run;
index=7 char=20
index=19 char=20
index=20 char=20
index=21 char=20
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
93   
94   data _null_;
95     call symputx('parameter',cats("'",tranwrd(compress(symget('parameter'),'090A0C0DA0'x),',',"','"),"'"));
96   run;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
97   
98   %put &parameter;
'Sales',' Operations','   DataManaagement'
99   

[Reeza]

---

@Babloo wrote:

 Solution should handle any type of gibberish.

---

How do you know what's gibberish and what's important? Is it just trailing or leading? What about multiple internal spaces? What about special characters inside the text?

[Babloo]

It would be nice if the solution can handle multiple internal spaces or
special characters inside and outside text. I cannot control the values
coming from Webform hence I was asked to fix this issue in SAS.

[Quentin]

---

@Tom @Kurt_Bremser  using HEX format seem to be a beautiful solution. I tried with Tom's code but it's not removing the leading and trailing spaces which I introduced in the macro variable. Am I missing something? Solution should handle any type of gibberish.

2   %let parameter=  Sales, Operations,   DataManaagement     ;
83   
84   data _null_;
85      length parameter $32767;
86      parameter=symget('parameter');
87      length char $1 ;
88      do index=1 to length(parameter);
89          char=char(parameter,index);
90          if not (' ' < char <= '~') then put index= char= $hex2. ;
91      end;
92   run;
index=7 char=20
index=19 char=20
index=20 char=20
index=21 char=20
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

The goal of this code is to find the unexpected / bad characters.  

I can't remember if you are expecting blanks or not.  I think the thought was that you had accidental control characters in your data (tabs, CR, LF, etc).

This:

if not (' ' < char <= '~') then put index= char= $hex2. ;

will print any blanks or non-printable characters using hex format.

This:

index=7 char=20
index=19 char=20
index=20 char=20
index=21 char=20

Is telling you that at positions 7, 19, 20, and 21 in your value, you have a space.  The hex value 20 is the code for a space.  You can use a table like https://www.ascii-code.com/ to look up ascii codes.

If you want to look for only non-printable characters, you could change the code to:

if not (' ' <= char <= '~') then put index= char= $hex2. ;

If you run that on your real data, the log should show you the hex values of any control characters that are found.

That said, if your goal is to ignore any kind of gibberish, not a certain kind of gibberish, you might want to use a different approach.  For example, you could use the COMPRESS() function to keep only the letters, or only letters and numbers, or whatever your definition of non-gibberish is.

[Tom]

So you values have SPACES (no special characters) between the comma and the next item in the list.

If you don't want those spaces then you are probably better off using one of the methods that loops through the items in the list and have it use STRIP() to remove both leading and trailing spaces.

357  %let parameter=  Sales, Operations,   DataManaagement     ;
358
359  data _null_;
360     length in out $32767;
361     in=symget('parameter');
362     do index=1 to countw(in,',');
363       out=catx(',',out,quote(strip(scan(in,index,',')),"'"));
364     end;
365     call symputx('parameter',out);
366  run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


367
368  %put &=parameter;
PARAMETER='Sales','Operations','DataManaagement'

Which is EXACTLY what %QLIST() is designed to do take a space delimited list of values and turn into a list of quoted values with parentheses around them so you can use them to subset your data.

454  %let parameter=  Sales, Operations,   DataManaagement     ;
455  %put where variable in %qlist(%superq(parameter),%delim=%str(,));
where variable in ('Sales','Operations','DataManaagement')

You should probably add this to top of your SAS program to add the macro quoting to the value of PARAMETER before you start using it

%let parameter=%superq(parameter);

Commas in macro variable values just cause TROUBLE.

[Babloo]

@Tom Below call symputx is not removing the spaces (or any special characters in it) in the value of the macro variable. The goal of the code should be to find and remove the unexpected / bad characters and enclose each string with single quotes separated by comma.

data _null_;
  call symputx('parameter',cats("'",tranwrd(compress(symget('parameter'),'090A0C0DA0'x),',',"','"),"'"));
run;

Log:

81   %let parameter=  Sales, Operations,   DataManaagement     ;
82   
83   data _null_;
84      length parameter $32767;
85      parameter=symget('parameter');
86      length char $1 ;
87      do index=1 to length(parameter);
88          char=char(parameter,index);
89          if not (' ' < char <= '~') then put index= char= $hex2. ;
90      end;
91   run;
index=7 char=20
index=19 char=20
index=20 char=20
index=21 char=20
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
92   
93   data _null_;
94     call symputx('parameter',cats("'",tranwrd(compress(symget('parameter'),'090A0C0DA0'x),',',"','"),"'"));
95   run;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      
96   
97   %put &parameter;
'Sales',' Operations','   DataManaagement'
98   
99   

[Tom]

I answered that question already.

The macro variable does not contain any of the characters that the compress() function call is trying to remove. So it does not make any changes.  

The trick of simply replacing the delimiter character with a quoted delimiter character does not work if there are extra characters around the delimiters that you do not want to become part of the new quoted values.

But in this example the actual value does not contain any spaces (you have "DataManaagement" instead of "Data Manaagement") so you could just include the space character into the list of values to be compressed away.

97   data _null_;
98     call symputx('parameter',cats("'",tranwrd(compress(symget('parameter'),'090A0C0D20A0'x),',',"','"),"'"));
99   run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


SYMBOLGEN:  Macro variable PARAMETER resolves to 'Sales','Operations','DataManaagement'
100  %put &parameter;
'Sales','Operations','DataManaagement'

Note that the step that is printing the strange characters to the log is only needed for debugging.  If you wanted to include it in your actual program you should modify it so it knows which ones will NOT be removed.

120  data _null_;
121     length parameter $32767;
122     parameter=symget('parameter');
123     length char $1 ;
124     do index=1 to length(parameter);
125         char=char(parameter,index);
126         if not (' ' < char <= '~') then do;
127           put index= char= $hex2. @;
128           if indexc(char,'090A0C0D20A0'x) then put ' will be removed';
129           else put;
130         end;
131     end;
132  run;

index=7 char=20  will be removed
index=19 char=20  will be removed
index=20 char=20  will be removed
index=21 char=20  will be removed

[Kurt_Bremser]

Please look at my code, and what it tries to tell you. The PUT statement will show the hex codes for the contents in the macro variable. You can then use the same codes (e.g. "0A"x) to filter these characters out, so they do not interfere with the TRIM function.

Knowing the hexadecimal codes of the ASCII table is a must for every aspiring coder, regardless of programming language.

[Tom]

---

@Babloo wrote:
I get values from JavaScript. Javascript will get the values from Webpage
prompt selection.

---

Who wrote the Javascript and the Web Form?  Have them fix it so instead of returning NAME when you pick NAME from the user interface it returns 'NAME'.   Web forms do that type of thing all of the time.  The user picks between Yes and No and the form returns Y or N, or perhaps 1 or 0.  It should be a trivial change to the Javascript.

[PaigeMiller]

You can use the %QLIST macro to take your input string in &parameter and create a new macro variable with single quotes around each item in &parameter.

https://github.com/sasutils/macros/blob/master/qlist.sas

[Babloo]

Aww, it's huge. It would be nice if it can be handled in much simpler way
in order for other team members also can understand.

[Reeza]

That's because it's generic for multiple cases. If your case is always this, go through and remove the parameters not needed and conditions not needed.

[PaigeMiller]

---

@Babloo wrote:
Aww, it's huge. It would be nice if it can be handled in much simpler way
in order for other team members also can understand.

---

While I understand why your other team members might need to understand your code, I don't understand the "it's huge" part. The code behind PROC SQL is huge as well, but you use it. The code behind PROC MEANS is huge, but you use it. And it gets the job done quickly, one line of code to %include the macro and one more line of code to call it and get the single quotes the way you want doesn't seem to be too burdensome.

And a well commented program makes it much easier for others to understand, even if they have never seen %qlist before.

[Quentin]

---

@Babloo wrote:
Aww, it's huge. It would be nice if it can be handled in much simpler way
in order for other team members also can understand.

---

This might be a good time to introduce your team to the benefits of having a shared autocall macro library which stores the definitions for general-purpose macros.

If your team has people writing macros, they will probably agree that many macro language use-cases involve lists.  I have a list of variables, and I want to loop over them.  Or a list of datasets.  Or list of whatever.  Often you want to do something to each item of the list, sometimes you want to generate a different list.  Maybe it's a list with the each item in quotes, or a list with a different delimiter, or a list where each item has  prefix or suffix added to it.

Once you see that this is a general problem of list modification, having a general macro you can call for list manipulation is very handy, and reduces the amount of code you need to write.  The function-style macro hides the code.  So whether you use %qlist, or Richard DeVenezia's %seplist (https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist), or something similar, if your team members are going to be using the macro language, it's worth taking the time to understand how function-style macros can make your life easier.

This 1 1/2 page paper by Peter Crawford is my favorite introduction to function-style macros.  It features a one-line macro.  https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/038-31.pdf

[Reeza]

/*source macro code - or download and maintain the code*/
filename qlist url 'https://raw.githubusercontent.com/sasutils/macros/master/qlist.sas';
%include  qlist;

%let varlist = Data Management,Operations,Sales; 

%let my_list= %qlist(%quote(&varlist.), paren=0, comma=1, delimit=',', dsd = 0, quote=2);

%put &my_list.;