topic Re: Calculate the average of a variable from two different rows in New SAS User

Calculate the average of a variable from two different rows

GLucio — Tue, 07 Jan 2020 10:45:49 GMT

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 😉

Re: Calculate the average of a variable from two different rows

FreelanceReinh — Tue, 07 Jan 2020 11:01:15 GMT

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;

Re: Calculate the average of a variable from two different rows

GLucio — Tue, 07 Jan 2020 11:07:57 GMT

Thanks a lot 😉 It works perfectly