Calculating monthly total returns for each investor

deleted_user — Thu, 26 Feb 2009 08:18:45 GMT

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?

Re: Calculating monthly total returns for each investor

deleted_user — Thu, 26 Feb 2009 08:39:57 GMT

I already found an answer to my problem...

I found it from the thread "Proc means summary of "duplicate" values"
http://support.sas.com/forums/thread.jspa?threadID=4874&tstart=15

proc means nway data = abc missing noprint;
var R ;
class ID CYMD ;
output out = bcd ( drop= _TYPE_ _FREQ_ )
sum = RMTOT;
run ;

Thank you guys!

topic Calculating monthly total returns for each investor in SAS Procedures

Calculating monthly total returns for each investor

Re: Calculating monthly total returns for each investor