- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
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;
- 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
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