DATA Step, Macro, Functions and more

Search/replace with special characters in macro variable

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Search/replace with special characters in macro variable

Hello,

I have a macro variable that stores a comma-separated list of field names, e.g.

%let mynumbers = a,b,c;

Now I would like to do a search/replace to get a new macro variable like this

mynumbers_sum = sum(a),sum(b),sum(c)

Here's a two-step solution that seems to work, however it is easy to see why I'm not happy with it:

/* step 1: add "sum(" and ")" at both ends and use tranwrd to replace commas by placeholder */

%let mynumbers_1 = sum(%sysfunc(tranwrd(%nrbquote(&mynumbers),%str(,),91sum6)));

/* step 2: use translate to replace each character in placeholder */

%let mynumbers_sum = %sysfunc(translate(%str(&mynumbers_1), ")", "9", ",", "1", "(", "6"));

Can someone suggest a better/more elegant solution that does not rely on characters I'm not using in my macro variable values? Thanks!


Accepted Solutions
Solution
‎01-17-2014 01:13 AM
Respected Advisor
Posts: 3,887

Re: Search/replace with special characters in macro variable

Just another way of doing it:

%let mynumbers = a,b,c, d_5a;
%put %sysfunc(prxchange(s/(\w+)/sum(\1)/oi,-1,%bquote(&mynumbers)));

sum(a),sum(b),sum(c), sum(d_5a)

View solution in original post


All Replies
Respected Advisor
Posts: 3,777

Re: Search/replace with special characters in macro variable

I would use PROC SUMMARY no need to transform the list.

%let mynumbers=a b c;

proc summary;

   var &mynumbers;

   output sum=;

   run;

Frequent Contributor
Posts: 83

Re: Search/replace with special characters in macro variable

Why not write it as an array, granted you would have to remove the commas, but it would solve the problem.

%let mynumbers = a b c;

data want;

     set have;

     array fir (*) &mynumbers;

     do i = 1 to dim(fir);

     mynumsum = sum (of fir(*));

     end;

     drop i;

run;

Frequent Contributor
Posts: 83

Re: Search/replace with special characters in macro variable

Additionally, if you really don't want to take out the commas you could use it in proc sql.

%let mynumbers=a,b,c;

proc sql;

create table want as

select *, sum(&mynumbers) as sumnum

from have;

quit;

Respected Advisor
Posts: 3,777

Re: Search/replace with special characters in macro variable

Are asking me?

overmar wrote:

Why not write it as an array, granted you would have to remove the commas, but it would solve the problem.

%let mynumbers = a b c;

data want;

     set have;

     array fir (*) &mynumbers;

     do i = 1 to dim(fir);

     mynumsum = sum (of fir(*));

     end;

     drop i;

run;

Frequent Contributor
Posts: 83

Re: Search/replace with special characters in macro variable

Sorry no, i was using the phrase as a juxtaposition and suggesting another alternative as your solution would also work.

Super Contributor
Posts: 282

Re: Search/replace with special characters in macro variable

Hi,

I'm not sure how or where you want to use the macro variable mynumbers_sum, but for the text substitution you could use:

/* take a comma separated list and add sum() around items */

%macro sumlist(list);

  %if not %index(%quote(&list),%str(,)) %then

    sum(&list);

  %else

    sum(%scan(%quote(&list),1)),%sumlist(%quote(%substr(%quote(&list),%eval(%index(%quote(&list),%str(,))+1))));

%mend sumlist;

/* test macro sumlist */

%let mynumbers = a,b,c;

%let mynumbers_sum=%sumlist(%quote(&mynumbers));

%put mynumbers_sum=&mynumbers_sum;

Regards,

Amir.

Super User
Super User
Posts: 6,499

Re: Search/replace with special characters in macro variable

Just quote it properly.

%let mynumbers = a,b,c;

%put sum(%sysfunc(tranwrd(%superq(mynumbers),%str(,),%str(%),sum%())));

sum(a),sum(b),sum(c)

Solution
‎01-17-2014 01:13 AM
Respected Advisor
Posts: 3,887

Re: Search/replace with special characters in macro variable

Just another way of doing it:

%let mynumbers = a,b,c, d_5a;
%put %sysfunc(prxchange(s/(\w+)/sum(\1)/oi,-1,%bquote(&mynumbers)));

sum(a),sum(b),sum(c), sum(d_5a)

Super Contributor
Posts: 376

Re: Search/replace with special characters in macro variable

My advice:  don't use commas in macro variable lists.  You just open yourself to a world of headaches.

Instead, store your list as a (non-comma) delimited list.  If your list data contains spaces (eg. file paths on Windows), use some other delimiter.  Then, use a helper macro, and add the commas at "run time" when you need them.

I'm a huge fan of Richard Devenezia, and I encourage you to visit his macros website:  SAS Macros - by Richard A. DeVenezia -

In particular, download the %seplist macro.  It makes it dead easy to switch between data step (space delimited) and SQL (comma delimited) lists.

I've also attached a %loop macro which I wrote - there are various versions "out there".  Remove the call to %parmv.

If you have either of these macros compiled, your code then becomes:

%let list=a b c;

%put %seplist(&list);

%put %seplist(&list,prefix=sum%str(%(), suffix=%str(%)));

%macro code;

%if &__iter__ gt 1 %then ,;

sum(&word)

%mend;

%put %loop(&list);

%let list=a list ^ containing spaces;

%put %seplist(&list,indlm=^);

%put %seplist(&list,indlm=^,prefix=sum%str(%(), suffix=%str(%)));

%put %loop(&list,dlm=^);


Output:


15         %let list=a b c;

16        

17         %put %seplist(&list);

a,b,c

18         %put %seplist(&list,prefix=sum%str(%(), suffix=%str(%)));

sum(a),sum(b),sum(c)

19        

20         %macro code;

21         %if &__iter__ gt 1 %then ,;

22         sum(&word)

23         %mend;

24         %put %loop(&list);

sum(a) , sum(b) , sum(c)

25        

26         %let list=a list ^ containing spaces;

27         %put %seplist(&list,indlm=^);

a list,containing spaces

28         %put %seplist(&list,indlm=^,prefix=sum%str(%(), suffix=%str(%)));

sum(a list),sum(containing spaces)

29         %put %loop(&list,dlm=^);

sum(a list) , sum(containing spaces)

Hope this helps...

Attachment
Occasional Contributor
Posts: 5

Re: Search/replace with special characters in macro variable

So many different ways of doing it... thanks everyone, that helped me a lot. Wish I could mark more correct/helpful answers. Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 2530 views
  • 14 likes
  • 7 in conversation