BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
titania
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

10 REPLIES 10
data_null__
Jade | Level 19

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

%let mynumbers=a b c;

proc summary;

   var &mynumbers;

   output sum=;

   run;

overmar
Obsidian | Level 7

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;

overmar
Obsidian | Level 7

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;

data_null__
Jade | Level 19

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;

overmar
Obsidian | Level 7

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

Amir
PROC Star

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.

Tom
Super User Tom
Super User

Just quote it properly.

%let mynumbers = a,b,c;

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

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

Patrick
Opal | Level 21

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)

ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
titania
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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