Calculate the average of a variable from two different rows

[GLucio]

Hey there,

I have a quick doubt. I need to generate a column that is the result of another one, in this case Service_Fee, where the value of each cell should be the average between the value of the variable Outstanding in that row and the value of the previous row. And then multiple that value with a constant of 0.000625.

For example, how it should look like in SAS:

Row     Outstanding     Service_Fee
 1            6 000 000         3 750,00
 2          21 000 000         8 437,50 
 3          30 000.000       15 937,50

Service_fee (row=2) = ((21 000 000 + 6 000 000)/2)*0,000625 = 8 437,50 

Thanks in advance 😉

[FreelanceReinh · Accepted solution]

Hello @GLucio and welcome to the SAS Support Communities!

You can use the LAG function to refer to a preceding value:

data have;
input Outstanding;
cards;
6000000
21000000
30000000
;

data want;
set have;
Service_Fee=mean(Outstanding, lag(Outstanding))*0.000625;
run;

[GLucio]

Thanks a lot 😉 It works perfectly