## Group totals based on 3 month interval

Regular Contributor
Posts: 178

# Group totals based on 3 month interval

data test;

infile datalines;

input Year \$ Month \$ Dept1N Dept2N ;

return;

datalines;

2014 1Jun2014 2500 2100

2014 1Jul2014 2330 2220

2014 1Aug2014 1500 2140

2014 1Sep2014 2500 2670

2014 1Oct2014 4500 4100

2014 1Nov2014 2600 7100

;

run;

Output

 Year Month Dept1N Dept2N 2014 1Jun2014 2500 2100 2014 1Jul2014 2330 2220 2014 1Aug2014 1500 2140 2014 1Sep2014 2500 2670 2014 1Oct2014 4500 4100 2014 1Nov2014 2600 7100

I need to add a 3 month Avg column based on the following

Dept1N(sum of the 1st 3 rows, then 2nd 3 rows...etc.  Example 2500+2330+1500=6330)

Dept2N(2100+2220+2140=6460)

To get my 3 month avg 6460/12790 = 50.50

Can this be best handles in proc report or here in the datastep and how??

Thus the calculation needs to evaluate every 3 months

Super User
Posts: 23,689

## Re: Group totals based on 3 month interval

[ Edited ]

In a data step use the LAG function if you are sure you have no missing months. If you have missing months, a SQL query or PROC EXPAND are your best option.

Do you have missing months that you need to deal with?

If no,then the following is probably the quickest method:

``average3 = currentVar + lag(currentVar) + lag2(currentVar);``

EDIT: I suppose if this is an average you should divide by 3 or use the MEAN function.

``average3 = mean(curentVar, lag1(currentVar), lag2(currentVar));``

Contributor
Posts: 23

## Re: Group totals based on 3 month interval

If my understanding of your question is correct, this should get you 80% of the way there. I don't understand the examples you gave. You listed the 3 month sum for department 2 divided by the 3 month sum for both departments and called it average? I believe this calculation is the percentage of department 2's three-month sales. Anyway, here is the code to append the static 3 month averages to the base file:

``````data test;
infile datalines;
input Year \$ Month \$ Dept1N Dept2N ;
return;
datalines;
2014 1Jun2014 2500 2100
2014 1Jul2014 2330 2220
2014 1Aug2014 1500 2140
2014 1Sep2014 2500 2670
2014 1Oct2014 4500 4100
2014 1Nov2014 2600 7100
;
run;

*Create identifiers for each 3 month segment;
data want;
set test;
three_month_count=ceil(_n_/3);
run;

*Calculate static three month averages for each department;
proc summary data = want nway nmiss;
class three_month_count;
var Dept1N Dept2N;
output out = want2 ( drop = _type_ _freq_ ) mean = avg_Dept1N avg_Dept2N;
run;

*Merge the three month averages back to the base file;
data append_averages;
merge want want2;
by three_month_count;*Add any calculations you need here;
run;``````
PROC Star
Posts: 509

## Re: Group totals based on 3 month interval

Best way would be do by @Reeza suggestion.

But yu can achieve same by proc sql as shown below

```proc sql;
select *,
sum(dept1N) as dept1_3months_total,
sum(dept2N) as dept2_3months_total,
calculated dept1_3months_total+ calculated dept2_3months_total as dept1_dept2total,
(calculated dept1_3months_total/calculated dept1_dept2total)*100 as mothavg
from
(select
min(month) as min_date format =date9. from test) a cross join
(select * from test)b
group by ceil((intck('month', min_date, month)+1)/3);
```
Discussion stats
• 3 replies
• 188 views
• 0 likes
• 4 in conversation