06-27-2017 02:43 PM
input 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)
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
06-27-2017 03:02 PM - edited 06-27-2017 03:03 PM
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));
06-27-2017 04:53 PM
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;
06-27-2017 05:00 PM
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);