topic Re: Moving sum by 12 months * moving weight by 3months in SAS Programming

Moving sum by 12 months * moving weight by 3months

Dinslax — Tue, 30 Jul 2019 09:47:07 GMT

Hi ,

Looking for a moving weight for a moving sum.

I know how to calculate a moving 12 month sum. but dont's know to give it a moving weight.

Here the example:

Period	Sales	Month	Moving SUM (12 Month)	Wanted weight for 201812	Wanted weight for 201901	Wanted weight for 2018902
201801	684,94	1		1
201802	515,42	2		1	1
201803	894,1	3		1	1	1
201804	7,43	4		2	1	1
201805	129,75	5		2	2	1
201806	829,85	6		2	2	2
201807	168,86	7		3	2	2
201808	55,17	8		3	3	2
201809	61,55	9		3	3	3
201810	867,91	10		4	3	3
201811	669,72	11		4	4	3
201812	258,08	12	5142,78	4	4	4
201901	454,17	1	4912,01		4	4
201902	861,42	2	5258,01			4

Can somebody help me?

Re: Moving sum by 12 months * moving weight by 3months

Dinslax — Tue, 30 Jul 2019 09:51:33 GMT

This is the code for the moving SUM:

/* Create monthly sales data from January 2008 to December 2010 */
data test;
do mo_period = '01jan2008'd to '31dec2010'd;
sales = round(ranuni(1234567) * 1000, .01);
mo_period = intnx('month', mo_period, 0, 'END');
output;
end;
format mo_period monyy7. sales comma10.2;
run;

/* Specify the number of periods in the rolling sum and average */
%let roll_num = 12;

data new;
set test;
/* Create array with specific number of elements. */
/* Passing _temporary_ arrays as arguments to functions is not supported */
/* until SAS 9.2. */
/* If the array is a _TEMPORARY_ array, the elements are automatically retained */
array summed[&roll_num] _temporary_;

/* Alternatively, a non-temporary array can be used but it must be retained: */
/* array summed[&roll_num];*/
/* retain summed;*/

/* E represents the element of the array to assign a sales value. */
/* Increment it by one unless it is equal to &roll_num, at which point */
/* start over and assign it a value of 1. This causes the oldest period to */
/* be replaced by the newest period once &roll_num periods have been read. */
if E = &roll_num then E = 1;
else E + 1;

/* Assign value to proper element of the array */
summed[E] = sales;

/* Start summing once &roll_num values have been read from the data set */
if _N_ >= &roll_num then do;
roll_sum = sum(of summed[*]);
roll_avg = mean(of summed[*]);
end;
format roll_sum roll_avg comma10.2;
run;

Re: Moving sum by 12 months * moving weight by 3months

PaigeMiller — Tue, 30 Jul 2019 12:13:19 GMT

Moving sum by 12 months is easily calculated using PROC EXPAND. If you don't have PROC EXPAND in your SAS license, you can find workaround here by searching these forums.

As far as your weights are concerned

data weights;
    set have;
    weight1 = floor((month-1)/3)+1;
    weight2 = lag(weight1);
    weight3 = lag2(weight1);
run;