topic Re: Search/replace with special characters in macro variable in SAS Programming

Search/replace with special characters in macro variable

titania — Thu, 16 Jan 2014 10:05:54 GMT

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!

Re: Search/replace with special characters in macro variable

data_null__ — Thu, 16 Jan 2014 12:06:14 GMT

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

%let mynumbers=a b c;

proc summary;

var &mynumbers;

output sum=;

run;

Re: Search/replace with special characters in macro variable

overmar — Thu, 16 Jan 2014 16:50:22 GMT

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;

Re: Search/replace with special characters in macro variable

overmar — Thu, 16 Jan 2014 16:51:17 GMT

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;

Re: Search/replace with special characters in macro variable

Amir — Thu, 16 Jan 2014 17:35:43 GMT

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.

Re: Search/replace with special characters in macro variable

data_null__ — Thu, 16 Jan 2014 17:56:53 GMT

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;

Re: Search/replace with special characters in macro variable

overmar — Thu, 16 Jan 2014 18:15:36 GMT

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

Re: Search/replace with special characters in macro variable

Tom — Fri, 17 Jan 2014 01:35:04 GMT

Just quote it properly.

%let mynumbers = a,b,c;

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

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

Re: Search/replace with special characters in macro variable

Patrick — Fri, 17 Jan 2014 06:13:18 GMT

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)

Re: Search/replace with special characters in macro variable

ScottBass — Fri, 17 Jan 2014 14:48:49 GMT

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;

17 %put %seplist(&list);

a,b,c

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

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

20 %macro code;

21 %if &__iter__ gt 1 %then ,;

22 sum(&word)

23 %mend;

24 %put %loop(&list);

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

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

Re: Search/replace with special characters in macro variable

titania — Thu, 23 Jan 2014 12:58:52 GMT

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