topic Re: sum by every X group in SAS Programming

sum by every X group

fierceanalytics — Thu, 29 Sep 2022 19:53:06 GMT

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.

Re: sum by every X group

ballardw — Thu, 29 Sep 2022 20:27:14 GMT

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.

Re: sum by every X group

fierceanalytics — Thu, 29 Sep 2022 20:46:54 GMT

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.

Re: sum by every X group

FreelanceReinh — Thu, 29 Sep 2022 20:56:09 GMT

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

Re: sum by every X group

Astounding — Thu, 29 Sep 2022 22:00:09 GMT

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;

Re: sum by every X group

ballardw — Thu, 29 Sep 2022 22:13:58 GMT

@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;

Re: sum by every X group

fierceanalytics — Thu, 29 Sep 2022 23:53:35 GMT

Thanks. Better than mine.