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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.