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 ( "¶meter" ) ; 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;
@Babloo wrote:
@PaigeMiller Like this,
filename qlist url 'https://raw.githubusercontent.com/sasutils/macros/master/qlist.sas'; %include qlist; %let varlist = Data Management,Operations,Sales; %let varlist = %qlist(%quote(&varlist.), paren=0, comma=1, delimit=',', dsd = 0, quote=2); %put &varlist.;
Since you're dealing with values passed in from elsewhere, you might want to change to using %superq() instead of %quote() to mask any macro triggers, and also change to passing quote=1 to get single quotes around each item of the list. Also note that you don't have to assign the new quoted list to a macro variable at all. You can just call %qlist where you want to generate the quoted list, e.g.:
options mprint ;
data _null_ ;
call symputx('varlist','Data Management,Operations,Sales,Franks&Beans') ;
run ;
%put varlist=%superq(varlist) ;
proc print data=have noobs;
where Division in ( %qlist(%superq(varlist), paren=0, comma=1, delimit=',', dsd = 0, quote=1) ) ;
run;
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.
Or can be tried using PRXCHANGE
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?
Then use a simple data step to get the quoted list:
data _null_;
length target $32767;
source = "¶meter.";
do i = 1 to countw(source,",");
target = catx(",",target,quote(scan(source,i,",")));
end;
call symputx('parameter',target,'g');
run;
Later, use
in (¶meter.)
@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 = "¶meter."; 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: ¶meter.; ####parameter is: "107171-003 "
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"
@Tom I tried your code and still the issue persists.
data _null_; 19 length target $32767; 20 source = "¶meter."; 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: ¶meter.; ####PARAMETER is: "SALES-100A-TS "
So you have some extra stuff in the parameter. Get to Know Your Data (Maxim 3):
data _null_;
length target $32767;
source = "¶meter.";
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;
@Kurt_Bremser Any other solution without using $hex format?
@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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: