sum by every X group

[fierceanalytics]

Hello, 

To illustrate,  data set has 27 records,  two variables, Month and Dollar. Month has 27 nonmissing month values. Dollar is a numeric. Sorted the data set by Month ascendingly. Do not care if months are consecutive or not, but physical order from lower to higher is important. So after sorting, create a new order: keyx=_n_;. Now keyx= has 1.... 27, the order is verified correct. 

How to sum Dollar by 1-3, 4-6,.... 25-27, three entries each? In other words, result will 9 sums, instead of 27, kind of skip sum by X group?  Thank you. 

[ballardw]

It would be a good idea to provide an example of your data in the form of a data step .

And exactly what the final data set would look like.

Any time that "Month" or other date, time or datetime information is mentioned there may be opportunities to use SAS supplied tools for dealing with them. But an actual example of the values involved is good idea to provide best or sometimes even just good, practices.

[fierceanalytics]

Let me further simply. 

Go by _n_ order (forget about that Month field). When _n_ =1 to 3, first sum of dollar field. when _n_=4 to 6, second sum.  how to order _n_ using month or other field is not the subject here. There are 27 rows originally. So look for 9 sums. 

I can use 'clumsy ways' to get it done. like to hear some elegant idea. Thank you. 

[FreelanceReinh]

Hello @fierceanalytics,

As a start, try this:

/* Create sample data for demonstration */

data have;
call streaminit(27182818);
month='01JAN2016'd;
do keyx=1 to 27;
  month=intnx('month',month,rand('integer',4));
  dollar=round(rand('uniform',1,9),0.1);
  output;
end;
format month yymmd7. dollar dollar12.2;
run;

%let X=3;

/* Create sums of DOLLAR for groups of X consecutive observations */

data want(keep=range sum);
length range $7;
range=cat(put(&X*(_n_-1)+1,2.),' - ',put(&X*_n_,2.));
do i=1 to &X;
  set have;
  sum=sum(sum,dollar);
end;
format sum dollar12.2;
run;

(But I wrote this before you requested an "elegant idea.")

[Astounding]

Elegance is in the eye of the beholder. Here is one approach once your data set is sorted.

data want;
groupnum + 1;
sum = 0;
do i=1 to 3;
set have;
sum + dollars;
end;
keep groupnum sum;
run;

[ballardw · Accepted solution]

---

@fierceanalytics wrote:

Let me further simply. 

Go by _n_ order (forget about that Month field). When _n_ =1 to 3, first sum of dollar field. when _n_=4 to 6, second sum.  how to order _n_ using month or other field is not the subject here. There are 27 rows originally. So look for 9 sums. 

I can use 'clumsy ways' to get it done. like to hear some elegant idea. Thank you. 

---

Try this using a data set I create with exactly 27 values.

data junk;
  do i= 1 to 27;
     value= rand('integer',20);
     output;
   end;
run;

data want;
   set junk;
   total=sum(lag(value),lag2(value),lag3(value));
   if mod(_n_,3)=0;
run;

[fierceanalytics]

Thanks. Better than mine.