topic Re: Group totals based on 3 month interval in SAS Programming

Group totals based on 3 month interval

Q1983 — Tue, 27 Jun 2017 18:43:35 GMT

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)

Add Dept1N+Dept2N (6330+6460=12790

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

Re: Group totals based on 3 month interval

Reeza — Tue, 27 Jun 2017 19:03:23 GMT

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

Re: Group totals based on 3 month interval

Rwon — Tue, 27 Jun 2017 20:53:13 GMT

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;

Re: Group totals based on 3 month interval

kiranv_ — Tue, 27 Jun 2017 21:00:37 GMT

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