Loop macro 100 times

[whoskeyinlima]

Is there a way to loop this macro to extend the process up to 100. Using the current code, one would have to repeat increment macro 100 times.

data have;
length num total 3.;
input num total 3.;
datalines;
1 10
2 20
3 30
4 40
5 50
;

%macro increment (from=, to=);

proc sql;
create table want_&from. as select
"from &from. to &to." as desc, sum(total) as new_total
from have where num ge &from. and num le &to.
group by desc
;quit;

%mend;
%increment (from=1, to=2);
%increment (from=2, to=3);
%increment (from=3, to=4);
%increment (from=4, to=5);

[PaigeMiller]

First, I question whether or not a macro is needed here. Usually, the idea of pulling data sets apart like this is completely unnecessary, as most analyses can be performed by using BY statements.

In  the event that you can't do whatever comes next with BY statements, first convince me that the above paragraph doesn't apply. Next step in creating a macro is to get the code to work for two iterations, in this case two different FROM values, without macros and without macro variables. It looks like you have errors in the code you show which must be fixed first; it looks like you haven't yet got the code working for two (or more) FROM values. Once that is working, show us the code and we can help further.

[whoskeyinlima]

The code works and here are the output for HAVE, WANT_1, and WANT_4.  I look forward to learn how the BY statement works here. Thanks.

[PaigeMiller]

What are you going to do with all these data sets once you create them? What analysis? What report? What next steps?

Or is the entire goal to get a table of sums for each value of &FROM?

[Reeza]

Optimal solution really depends on how to scale this. For example, if it really is just sum and adding up the prior row, then you could do it in a data step. If you have multiple records and just summing the two levels, also in a data step. There's a bunch of different ways. Not sure BY would work, but this isn't optimal by any means. If you explain your use case in more detail we can provide a better solution. Otherwise right now, this seems like the definition of an XY problem (https://xyproblem.info/)

[whoskeyinlima]

Thanks Reeza.  Yes, they didn't represent the real data, so I simplified and scaled down using those examples.  The summing level was just an addition that was not needed.  The goal with all those WANT_XX tables is to append them. If the images below represent what I have and want. The repeated increment macro works, but I'm not sure it's optimal.

  

[PaigeMiller]

---

@whoskeyinlima wrote:

Thanks Reeza.  Yes, they didn't represent the real data, so I simplified and scaled down using those examples.  The summing level was just an addition that was not needed.  The goal with all those WANT_XX tables is to append them. If the images below represent what I have and want. The repeated increment macro works, but I'm not sure it's optimal.

  

---

This is a simple application of PROC SUMMARY. By telling us you want to use a macro that does something 100 times, you have pointed yourself and us in a direction that isn't optimal — better would be to just describe the data and describe what you want to do with it, rather than starting by saying you want a macro.

data have1;
    set have;
    num1=floor(num);
run;
proc summary data=have1 nway;
    class num1;
    var total;
    output out=want sum=;
run;

[Reeza]

A data step also works 🙂

data have;
length num total 3.;
input num total 3.;
datalines;
1 10
2 20
3 30
4 40
5 50
;

data want;
set have;
prev_total = lag(total);
prev_num =lag(num);

if _n_ > 1 then do;
desc = 'from '|| trim(put(prev_num, 8. -l)) || " to " ||  put(num, 8. -l);
new_total = total+prev_total;
output;
end;

keep desc new_total;
run;

[PaigeMiller]

Yes it works. I am opposed to writing data step code to do things that are already programmed in a PROC. Too many people, especially beginners, seem to struggle with this, and so I do not recommend beginners writing data step code when a PROC will do the same thing. I know that you, @Reeza, can write such data step code in your sleep, with one hand tied behind your back, but beginners really ought to use the proper PROC.

[TomKari]

Just to include my $.02 worth, i) I think it's great that Reeza is showing an alternate way of doing it, but ii) I absolutely agree with Paige that less-experienced developers should always use a PROC, if possible.

Tom

[ballardw]

---

@whoskeyinlima wrote:

Thanks Reeza.  Yes, they didn't represent the real data, so I simplified and scaled down using those examples.  The summing level was just an addition that was not needed.  The goal with all those WANT_XX tables is to append them. If the images below represent what I have and want. The repeated increment macro works, but I'm not sure it's optimal.

  

---

Since you don't show any of the code for APPENDING all of these "increment" data sets I suggest that you really want to look at the solution I propose for the solution with a format because it has the "want" result without appending anything.

[Reeza · Accepted solution]

Here is the answer to your question, but like others have mentioned, this isn't a good idea. 

data _null_;
do i=1 to 99;
    str = catt('%increment(from =', put(i, 8. -l) , " , to = " , put(i+1, 8. -l), ");");
    call execute(str);
end;
run;

---

@whoskeyinlima wrote:

Is there a way to loop this macro to extend the process up to 100. Using the current code, one would have to repeat increment macro 100 times.

data have;
length num total 3.;
input num total 3.;
datalines;
1 10
2 20
3 30
4 40
5 50
;

%macro increment (from=, to=);

proc sql;
create table want_&from. as select
"from &from. to &to." as desc, sum(total) as new_total
from have where num ge &from. and num le &to.
group by desc
;quit;

%mend;
%increment (from=1, to=2);
%increment (from=2, to=3);
%increment (from=3, to=4);
%increment (from=4, to=5);

---

[whoskeyinlima]

Exactly what I needed.. So much appreciated!! 👌🙏

[ballardw]

Purpose of the code? Really tell us where you are going with this.

If the purpose is to group values by combinations of NUM to sum the TOTAL variable here's one way that does that.

data have;
length num total 3.;
input num total 3.;
datalines;
1 10
2 20
3 30
4 40
5 50
;

proc format;
value mygroups (multilabel)
1 - 2 = '1 to 2'
2 - 3 = '2 to 3'
3 - 4 = '3 to 4'
4 - 5 = '4 to 5'
1 - 3 = '1 to 3'
2 - 5 = '2 to 5'
;
run;

proc summary data=have nway;
   class num/mlf;
   format num mygroups.;
   var total;
   output out=want (drop=_type_ freq) sum=;
run;

proc tabulate data=have;
   class num/mlf;
   format num mygroups.;
   var total;
   table num,
         total*sum
         ;
run;

Formats can create groups of values that will be honored by many procedures. However there is a special class of formats called multilabel that allow multiple overlapping ranges. Only a few procedures can use them, Means/Summary Tabulate and Report, but when they work it can be quite slick, as above. I included these overlaps to create groups because the method you show is not limited to none overlapping values such as the 1 to 3 and 2 to 5 I included.

This format grouping approach is extremely flexible in that if I have to create a different set of groups I only need create another format and run the Proc Summary or Tabulate using the other format. No need to create another 100 (or 30 or 200) datasets, just a new format code. IF the limits you need exist in a data set or have the implied order (you show exactly 4 of "100") values for Num to group with it could be that the format process could be automated.

But really, tell us what the whole process is supposed to do.