Help using Base SAS procedures

Calculating monthly total returns for each investor

Reply
N/A
Posts: 0

Calculating monthly total returns for each investor

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?
N/A
Posts: 0

Re: Calculating monthly total returns for each investor

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!
Ask a Question
Discussion stats
  • 1 reply
  • 117 views
  • 0 likes
  • 1 in conversation