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!
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)
I would use PROC SUMMARY no need to transform the list.
%let mynumbers=a b c;
proc summary;
var &mynumbers;
output sum=;
run;
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;
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;
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;
Sorry no, i was using the phrase as a juxtaposition and suggesting another alternative as your solution would also work.
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.
Just quote it properly.
%let mynumbers = a,b,c;
%put sum(%sysfunc(tranwrd(%superq(mynumbers),%str(,),%str(%),sum%())));
sum(a),sum(b),sum(c)
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)
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...
So many different ways of doing it... thanks everyone, that helped me a lot. Wish I could mark more correct/helpful answers.
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 25. 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.