topic Re: Adding parenthesis to a Macro Variable in SAS Programming

Adding quotes to a Macro Variable

Learn_uk — Tue, 03 May 2016 23:51:59 GMT

Hello everyone,

If I have a macro variable and I would like to put quotation marks around it, is there a function to do this?

Thanks

Example:

Have: %let ppl= 001-001 001-002;

Want: "001-001" "001-002"

Re: Adding parenthesis to a Macro Variable

Reeza — Tue, 03 May 2016 21:40:22 GMT

I think you mean quotes.

Are you you creatimg this manually? How do you plan to use it? There are ways but not easily so sometimes easier to do depending on how you create macro variable or how you plan to use it.

Re: Adding parenthesis to a Macro Variable

Learn_uk — Tue, 03 May 2016 22:11:22 GMT

Thanks Reeza for your response. I am using it as follows:

proc sql;

select distinct subject into :ppl separated by ' '

from opt.sv

where foldername = "Early Study Withdrawal";

quit;

proc print data=opt.sv(where=(subject = &ppl));
var subject folder svdtc;
run;

ERROR: WHERE clause operator requires compatible variables.

Re: Adding parenthesis to a Macro Variable

Learn_uk — Tue, 03 May 2016 22:12:03 GMT

I dont know why my SQL code threw in a smiley face! 🙂

Re: Adding parenthesis to a Macro Variable

ballardw — Tue, 03 May 2016 23:21:25 GMT

I think you may want something like

proc sql;
   select distinct quote(strip(subject)) into :ppl separated by ' '
   from opt.sv
   where foldername = "Early Study Withdrawal";
quit;

proc print data=opt.sv(where=(subject in( &ppl)));
   var subject folder svdtc;
run;

The strip is to remove trailing blanks within the quotes which might cause issues with the comparison

And you didn't want to test if subject was equal to a list of values but a member of the list.

Re: Adding parenthesis to a Macro Variable

Tom — Wed, 04 May 2016 01:29:20 GMT

Note that STRIP() will also remove any existing leading spaces, which could cause values to not match. It is safer to use TRIM() in this case.

Re: Adding quotes to a Macro Variable

Ksharp — Wed, 04 May 2016 01:32:15 GMT

%let ppl= 001-001 001-002;
%let want="%sysfunc(tranwrd(%cmpres(&ppl),%str( ),%str(" ")))";

%put &want;

Re: Adding parenthesis to a Macro Variable

Learn_uk — Wed, 04 May 2016 15:17:03 GMT

Thanks so much BallardW.....that was super! Really appreciate your help. 🙂

Re: Adding quotes to a Macro Variable

Learn_uk — Wed, 04 May 2016 15:19:23 GMT

Thanks Xia for that very interesting method...I am going to have to figure out what each element is doing! Complicated but quite impressive! Thx again! 🙂

Re: Adding parenthesis to a Macro Variable

ballardw — Wed, 04 May 2016 16:31:15 GMT

Absolutely. I tend to forget that because my data routinely gets leading spaces removed.

Re: Adding quotes to a Macro Variable

Peter_C — Wed, 04 May 2016 18:43:07 GMT

Looks like you already have your answer, but I'd like to offer more features to support what you seem to seek.
1
The select into: clause provides a feature NOTRIM to maintain lead/trailing blanks - if you need those.
2
When selecting columns in a query, you can define an alternative format and here all those functions like trim/strip/quote etc become redundant if you use the $quote format like :

proc sql;
select distinct subject format= $quote200. into : pl separated by ', '
from opt.sv
where foldername = "Early Study Withdrawal"
;
quit;

That 200 length should accomodate a wide value of subject plus 2 for those quotation marks.
Beware that the $quote trims trailing blanks, but inside SAS data the comparison results would not be affected by that trim.

It is interesting to compare the quoting format and function
- One trims and the other does not.

Re: Adding quotes to a Macro Variable

Ksharp — Thu, 05 May 2016 07:43:30 GMT

Here is an alternative way by PRXCHANGE() .

%let ppl= 001-001 001-002;
%let want=%sysfunc(prxchange(s/(\S+)/"\1"/,-1,&ppl));

%put &want;

Re: Adding quotes to a Macro Variable

AbhiD — Wed, 27 Jul 2016 09:49:12 GMT

Hi,

just a small suggestion , when comparing text variable making them into same case, this will help resolve mis-match due to case inconsistency in the data..

Hope that helps

Rgds,

Abhi

Re: Adding quotes to a Macro Variable

Steelers_In_DC — Thu, 23 May 2019 13:07:04 GMT

I'm a big fan of this solution, it helps to use a macro variable date within teradata.

Excellent!

Re: Adding quotes to a Macro Variable

mansour_ib_sas — Thu, 23 May 2019 14:45:59 GMT

Hello,

I have this solution, the result is correct but, i have the error of evaluate.

Can someone help me understand his provenance?

Thank you

%macro tt();
%global f;

%let a=001-001 001-002;

%let f= %str( );

%do i=1 %to %sysfunc(countw(&a.,' '));

%let f=%sysfunc(catx(%str( ),&f.,"%scan(&a.,&i,' ')") );

%end;

%mend;
%tt;
%put =&f.;

///////

ERROR: %SYSEVALF function has no expression to evaluate.
         %put =&f.; ="001-001" "001-002"

Re: Adding quotes to a Macro Variable

FreelanceReinh — Thu, 23 May 2019 16:19:32 GMT

Hello @mansour_ib_sas,

When CATX is called for the first time (by %SYSFUNC), macro variable F contains only a blank, which is regarded equal to a null (i.e. empty) value in macro variable comparisons because trailing blanks are ignored. It appears to me that this lack of "character contents" triggers the %SYSEVALF function to evaluate &F numerically. After all, the CATX function does not only concatenate character strings, but also numeric arguments (which are converted to a character string, but only after evaluation), so &F could very well be a numeric expression which might contain non-integer operands and hence would need evaluation by %SYSEVALF. Calling %SYSEVALF with an empty argument, however, results in the error message you've reported (try %let x=%sysevalf();).

To avoid the error message you could apply %IF/%THEN logic to start with CATX calls only after F has been populated with the first "word" of the character string in macro variable A. However, I would recommend to perform the concatenation in the form

%let f=&f "%scan(&a,&i,%str( ))";

because this is simpler and doesn't require CATX at all (hence the error message cannot occur).

Note that I replaced your single blank in single quotes (' ') by %str( ) because those single quotes in a %SYSFUNC call would be interpreted as ordinary characters, here: delimiters, which is probably not what you intended. (So, please do the same in your COUNTW call.) Also, the initialization of macro variable F can be simplified to %let f=;.

Re: Adding quotes to a Macro Variable

Tom — Thu, 23 May 2019 16:39:45 GMT

There is no need to use the CAT... functions to concatenate macro variables. Just expand the values where you want them and they will automatically be concatenated. If you want something like a space between the values the just do:

%let f=&f "%scan(&a,&i,%str( ))";

So if F is already empty the leading space before the quote will be ignored by the %LET.

If you need to something other than space as the delimiter you might need to take special care to not add one in the front.

You could start your loop at 2.

%let f="%scan(&a,1,%str( ))";
%do i=2 %to ....

You could test and treat the first value differently.

What I like to do for this "catx" type structure is use a different macro variable to contain the separation character (string) and set it empty to start and then set it after the first "concatenation". Then no need for %IF logic.

For example to make a comma separated list.

%let f=;
%let sep=;
%do i=1 %to %sysfunc(countw(&a,%str( )));
  %let f=&f.&sep."%scan(&a,&i,%str( ))";
  %let sep=,;
%end;

Re: Adding quotes to a Macro Variable

mansour_ib_sas — Thu, 23 May 2019 19:24:38 GMT

Now it's good. Thank you for your pertinents comments

%macro tt();
%global f;
%let a=001-001 001-002;
%let f=;
%do i=1 %to %sysfunc(countw(&a.,%str( )));
%let f=&f. "%scan(&a.,&i,%str( ))" ;
%end;
%mend;
%tt;

%put f=&f.;