I have to calculate monthly total returns for each investor (ID) like
ID CYMD RMTOT
1 200201 0.06
1 200202 -0.04
1 200203 -0.03
1 200204 0.03
etc.
Here is an example of the data. IC represents assets, CYMD (year, month) and R monthly return of an asset.
ID IC CYMD R
1 1 200201 0.03
1 2 200201 0.02
1 3 200201 0.01
1 1 200202 0.01
1 2 200202 -0.05
1 1 200203 -0.05
1 2 200203 0.02
1 1 200204 0.02
1 4 200204 0.01
2 1 200201 0.03
2 2 200201 0.02
2 3 200201 0.01
2 1 200202 0.01
2 2 200202 -0.05
2 3 200202 -0.05
As you see assets and investing time may variate over time.
I tried to do it by calculating a new variable RPMTOT. Obviously when reading the data and setting a new variable you may not use the lag-function.
data bcd ;
set abc ;
if CYMD ^= lag( CYMD ) then RMTOT = R ;
else RMTOT = lag( RMTOT ) + R ;
keep ID IC CYMD R RMTOT ;
Now I would pick up the last RMTOT value for each month and for each investor to get the result I want.
I think there has to be easier and working way to do it but how it should be done?