Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- sum by every X group

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-29-2022 03:53 PM
(603 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

data want;

groupnum + 1;

sum = 0;

do i=1 to 3;

set have;

sum + dollars;

end;

keep groupnum sum;

run;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks. Better than mine.

Are you ready for the spotlight? We're accepting content ideas for **SAS Innovate 2025** to be held May 6-9 in Orlando, FL. The call is **open **until September 16. Read more here about **why** you should contribute and **what is in it** for you!

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.