topic Re: Macro Variable Quoting in SAS Programming

Macro Variable Quoting

Babloo — Thu, 25 May 2023 15:48:41 GMT

I've the macro variable value separated by commas. I want the each value to be enclosed with single quote while I use in program. Currently it's not working as it is enclosing with one quote for all the values instead of each value to be separated by single quote.

I cannot control the macro variable value in %let as it is being passed by other macro variable. I want to know how to add single quote in WHERE clause.

%let parameter=Data Management,Operations,Sales ;
proc print data=have noobs;
  where Division in ( "&parameter" ) ;  
run;

Log shows,

where Division='Data Management,Operations,Sales';

Excepted Result:

Where Division= 'Data Management','Operations','Sales';

Solution should handle if even there is one value or two values in the %let.

E.g..

%let parameter=Data Management;
%let parameter=Data Management,Operations;

Re: Macro Variable Quoting

ballardw — Thu, 25 May 2023 16:19:24 GMT

If you need each value in a list to be in quotes then you really should make the variable that way.

%let parameter='Data Management','Operations','Sales' ;

You also need to consider if you really really really need the commas. The macro processor uses commas to delimit parameters in a macro definition. So you may get unexpected (read runtime errors) if you have a macro attempting to use that macro variable as a parameter. Many functions will also treat the comma as a delimiter and require special handling as well.

Do also note that this is incorrect syntax:

Where Division= 'Data Management','Operations','Sales';

IN perhaps but never = to a list of items in SAS syntax. That is not what SAS would generate in the log either:

162  %let namelist= 'Alice' 'Fred';
163
164  data want;
165     set sashelp.class;
166     where name in (&namelist.);
167  run;

NOTE: There were 1 observations read from the data set SASHELP.CLASS.
      WHERE name in ('Alice', 'Fred');
NOTE: The data set WORK.WANT has 1 observations and 5 variables.

You log shows = because there was only one value for the in operator to evaluate.

Re: Macro Variable Quoting

Kurt_Bremser — Thu, 25 May 2023 16:29:19 GMT

I strongly suspect that you won't be writing/rewriting the %LET every time you run such a program.

From where do you get the values to populate the macro variable?

Re: Macro Variable Quoting

Babloo — Thu, 25 May 2023 16:29:34 GMT

While I agree with you, please note that I cannot control the values passed
to %let parameter.

I'm puzzled how to resolve this issue now. Is there a way that we can
create another macro variable like %let parameter2 which should take value
from %let parameter and to add single quote to each value?

Re: Macro Variable Quoting

Babloo — Thu, 25 May 2023 16:31:34 GMT

I get values from JavaScript. Javascript will get the values from Webpage
prompt selection.

Re: Macro Variable Quoting

PaigeMiller — Thu, 25 May 2023 16:37:12 GMT

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

Re: Macro Variable Quoting

Babloo — Thu, 25 May 2023 16:43:34 GMT

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.

Re: Macro Variable Quoting

Reeza — Thu, 25 May 2023 16:45:30 GMT

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.

Re: Macro Variable Quoting

Kurt_Bremser — Thu, 25 May 2023 16:48:57 GMT

Then use a simple data step to get the quoted list:

data _null_;
length target $32767;
source = "&parameter.";
do i = 1 to countw(source,",");
  target = catx(",",target,quote(scan(source,i,",")));
end;
call symputx('parameter',target,'g');
run;

Later, use

in (&parameter.)

Re: Macro Variable Quoting

PaigeMiller — Thu, 25 May 2023 16:56:58 GMT

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

Re: Macro Variable Quoting

Tom — Thu, 25 May 2023 17:15:44 GMT

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

Re: Macro Variable Quoting

Reeza — Thu, 25 May 2023 19:02:38 GMT

/*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.;

Re: Macro Variable Quoting

AhmedAl_Attar — Thu, 25 May 2023 17:57:14 GMT

@Babloo

I typically do the following to quote separated values within a macro variable

%let parameter=Data Management,Operations,Sales;
%put &=parameter;
%let parameter=%str(%')%qsysfunc(tranwrd(&parameter,%str(,), %str(%',%')))%str(%');
%put &=parameter;

/* To use this macro variable in a where clause, I would use %unquote() */
Where Division in (%unquote(&parameter));

Hope this helps

Re: Macro Variable Quoting

Quentin — Thu, 25 May 2023 20:30:50 GMT

@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

Re: Macro Variable Quoting

Babloo — Fri, 26 May 2023 04:39:35 GMT

@Kurt_Bremser I tried your code and it's almost fine. One issue is ending quote is displaying in next line. I'm not certain about the likely cause for the issue.

E.g.

"107171-003

Log:

18   data _null_;
19   length target $32767;
20   source = "&parameter.";
SYMBOLGEN:  Macro variable parameter resolves to 107171-003
21   do i = 1 to countw(source,",");
22     target = catx(",",target,quote(scan(source,i,",")));
23   end;
24   call symputx('parameter',target,'g');
25   run;

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

SYMBOLGEN:  Macro variable parameter resolves to "107171-003

"
26   
27   %put ####parameter is: &parameter.;
####parameter is: "107171-003

"

Re: Macro Variable Quoting

Tom — Fri, 26 May 2023 04:51:24 GMT

It is likely the extra characters were in the source string.

That is the nature of fixed length strings used by data step.

If variable SOURCE is defined to be length 20 and you assign the value "107171-003" to then it will have 10 spaces added to fill it out to the full 20.

So when you use SCAN() to find the first word when delimited by comma it takes all 20 characters.

Which when passed to QUOTE() means you get back a string that is 22 characters long once the opening a closing quotes are added.

Use the TRIM() function to remove trailing spaces.

target = catx(",",target,quote(trim(scan(source,i,","))));

Or replace TRIM() with STRIP() if you also want to remove leading spaces from values like "107171-003, 10717-004"

Re: Macro Variable Quoting

Babloo — Fri, 26 May 2023 05:21:48 GMT

@Tom I tried your code and still the issue persists.

  data _null_;
19   length target $32767;
20   source = "&parameter.";
SYMBOLGEN:  Macro variable PARAMETER resolves to SALES-100A-TS
21   do i = 1 to countw(source,",");
22   /*   target = catx(",",target,quote(scan(source,i,","))); */
23     target = catx(",",target,quote(trim(scan(source,i,","))));
24   end;
25   call symputx('parameter',target,'g');
26   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-100A-TS

"
27   
28   %put ####PARAMETER is: &parameter.;
####PARAMETER is: "SALES-100A-TS

"

Re: Macro Variable Quoting

Kurt_Bremser — Fri, 26 May 2023 05:38:10 GMT

So you have some extra stuff in the parameter. Get to Know Your Data (Maxim 3):

data _null_;
length target $32767;
source = "&parameter.";
do i = 1 to countw(source,",");
  word = scan(source,i,",");
  put word $hex30.; * increase format width if needed;
  * later, add use of COMPRESS to get rid of unwanted special characters;
  target = catx(",",target,quote(trim(word)));
end;
call symputx('parameter',target,'g');
run;

Re: Macro Variable Quoting

Babloo — Fri, 26 May 2023 09:07:29 GMT

@Kurt_Bremser Any other solution without using $hex format?

Re: Macro Variable Quoting

Kurt_Bremser — Fri, 26 May 2023 10:57:30 GMT

It is the easiest way to find out which invisible characters are causing unwanted effects.