Macro Variable Quoting

[Babloo]

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;

[ballardw]

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.

[Babloo]

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?

[Vibha_GS]

Or can be tried using PRXCHANGE

[Kurt_Bremser]

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?

[Babloo]

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

[Kurt_Bremser]

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

[Babloo]

@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

"

[Tom]

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"

[Babloo]

@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

"

[Kurt_Bremser]

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;

[Babloo]

@Kurt_Bremser  Any other solution without using $hex format?

[Kurt_Bremser]

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

[Babloo]

@Kurt_Bremser I tried your code which has HEX format and it's almost fine. Sometimes the issue is resolved (ending quote is in same line) and sometimes the issue is ending quote is displaying in next line. I'm not certain about the likely cause for the issue.

E.g. 

"107171-003

"

Values are passed to SAS from  front end prompt value selection  in Webpage via Javascript. Hence I asked you the other solution. 

[Tom]

If using TRIM() didn't remove the white space then it does consist only of spaces.  There is some other character in there.

It is much easier to look for strange characters using SAS code than MACRO code. 

So get the value of the original macro variable into a SAS character variable.  Then remove all of the NORMAL characters.  Then just look at the hex codes of the remaining characters.

So if the macro variable is named PARAMETER you might do something like this:

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;

Some common "invisible" characters that might be in your macro variable are:

09 - TAB

0A - Line Feed

0C - Form Feed

0D - Carriage Return

A0 - "non-breaking" space

Once you know what type of gibberish your Web form is inserting into your parameter values you can design a solution to get rid of it.

Perhaps something like this:

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

Which says (reading from the inner most out).  Get the value of macro variable named PARAMETER. Remove any tabs, linefeeds, formfeeds, carriage returns and non-space spaces.  Replace the commas with quoted commas.  Add quotes on the ends.  Write the result into the macro variable PARAMETER.