Calculating moving average in sas

[langalife]

Good day

I have a task in creating a sum given the followng

ColA ColB

1       4

2       8

3      12

4      10

5      20

6      30

7     11

8     13

9     14

10    60

11   70

12   100

Frist i want to calculate from ColA frist 4 rows but on b from the 5th row in a manner below.

ColB        divided by ColA

Sum(20,30,11,13)/Sum(1,2,3,4)

Sum(30,11,13,14)/Sum(2,3,4,5)

Sum(11,13,14,60)/Sum(3,4,5,6) and so forth.

however i want to do this regardless of the number of row i also need a trigger to show the last row and it should stop.

Your help will be highly appreciated.

[PeterClemmensen]

@langalife Hi and welcome to the SAS Community 🙂

Do you need this to handle By-Groups? And do you have a SAS/ETS license?

[yabwon · Accepted solution]

Hi @langalife ,

do you mean something like below:

data have;
input ColA ColB;
cards;
1 4
2 8
3 12
4 10
5 20
6 30
7 11
8 13
9 14
10 60
11 70
12 100
;
run;

data want;

  set have;
  sum_a = 0;
  sum_a + (colA + lag1(colA) + lag2(colA) + lag3(colA));

  if _N_ >= 4;
  merge
    have(keep=colB rename=(colB=colNB)firstobs=5)
    have(keep=colB rename=(colB=colNBB)firstobs=6)
    have(keep=colB rename=(colB=colNBBB)firstobs=7)
    have(keep=colB rename=(colB=colNBBBB)firstobs=8)
  ;
  sum_b = sum(of colNB:);  
  drop colNB:;
  avg = sum_b / sum_a; 

run;
proc print;
run;

data want2;
  set have(obs=3) want;
run;
proc print;
run;

?

All the best

Bart

[PeterClemmensen]

One way ..

data have;
input ColA ColB;
datalines;
1  4
2  8
3  12
4  10
5  20
6  30
7  11
8  13
9  14
10 60
11 70
12 100
;

data want(keep=colA colB avg);
    array l [0:3] _temporary_;          
    
    do obs=1 by 1 until (lr);       
        merge have
              have(keep=colB rename=(colB=colB2)firstobs=2)
              have(keep=colB rename=(colB=colB3)firstobs=3)
              have(keep=colB rename=(colB=colB4)firstobs=4) end=lr;
        l [mod(obs, 4)] = lag1(ColA);         
        avg = sum(of colB:) / sum(of l [*]);              
        output;
    end;
run;

[Satish_Parida]

data have;
input ColA ColB;
cards;
1 4
2 8
3 12
4 10
5 20
6 30
7 11
8 13
9 14
10 60
11 70
12 100
;
run;

data want;
set have;
runing_averae=sum(colB,lag(colB),lag2(colB),lag3(colB))/sum(colA,lag(colA),lag2(colA),lag3(colA));
run;

Please let us know if this worked for you.

[Ksharp]

Better post then output you want see.

data have;
input ColA ColB;
cards;
1 4
2 8
3 12
4 10
5 20
6 30
7 11
8 13
9 14
10 60
11 70
12 100
;
run;
data want;
 merge have have(keep=colb rename=(colb=_colb) firstobs=5);
 laga=lag(cola);laga2=lag2(cola);laga3=lag3(cola);laga4=lag4(cola);
 lagb=lag(_colb);lagb2=lag2(_colb);lagb3=lag3(_colb);lagb4=lag4(_colb);
 if _n_>4 then want=sum(lagb,lagb2,lagb3,lagb4)/sum(laga,laga2,laga3,laga4);
run;