DATA Step, Macro, Functions and more

Compute cumulative return over future 6 months

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Compute cumulative return over future 6 months

Dear SAS communities, I have monthly returns and trying to compute future cumulative (compounded) returns for 1 month, 6 months, 12 months but I am having trouble. What if the return is missing at the beginning? Then we have to go to the next one. If return is missing in the middle we can use the one from the previous months. 

 

data have;
  infile datalines dsd truncover;
  input companyid:8. DATE:YYMMDDN8. RET:11.6;
  format companyid 8. DATE YYMMDDN8. RET 11.6;
  label companyid="companyid" DATE="Names Date" RET="Returns";
datalines4;
11132,19980130,
11132,19980227,0.187500
11132,19980331,0.013158
11132,19980430,0.051948
11132,19980529,-0.061728
11132,19980630,-0.078947
11132,19980731,0.014286
11132,19980831,-0.077465
11132,19980930,-0.206107
11132,19981030,0.096154
11132,19981130,0.192982
11132,19981231,-0.250000
11132,19990129,0.058824
11132,19990226,-0.074074
11132,19990331,-0.070000
11132,19990430,-0.182796
11132,19990528,0.171053
11132,19990630,0.258427
11132,19990730,-0.285714
11132,19990831,0.150000
11132,19990930,-0.130435
11132,19991029,-0.100000
11132,19991130,-0.083333
11132,19991231,0.333333
11132,20000131,0.113636
11132,20000229,0.561224
11132,20000331,-0.006536
11132,20000428,-0.342105
11132,20000531,-0.060000
11132,20000630,0.063830
11132,20000731,0.400000
11132,20000831,0.014286
11132,20000929,0.126761
11132,20001031,-0.050000
11132,20001130,-0.144737
11132,20001229,0.046154
11132,20010131,0.338235
11132,20010228,-0.010989
11132,20010330,-0.122222
11132,20010430,0.032911
11132,20010531,0.333333
11132,20010629,-0.029412
11132,20010731,0.272727
11132,20010831,-0.214286
11132,20010928,-0.246970
11132,20011031,0.096579
11132,20011130,0.405505
11132,20011231,0.023499
11132,20020131,0.052296
11132,20020228,-0.019394
11132,20020328,0.022250
11132,20020430,-0.146312
11132,20020531,0.239235
11132,20020628,-0.143331
11132,20020731,-0.052702
11132,20020830,0.042254
11132,20020930,-0.013514
11132,20021031,0.095890
11132,20021129,0.186250
11132,20021231,-0.052687
11132,20030131,-0.043381
11132,20030228,0.133721
11132,20030331,0.173333
11132,20030430,0.279720
11132,20030530,0.221994
11132,20030630,
11132,20030731,0.369231
11132,20030829,0.060674
11132,20030930,-0.136652
11132,20031031,-0.033129
11132,20031128,0.087563
11132,20031231,-0.212369
11132,20040130,-0.200494
11132,20040227,-0.095182
11132,20040331,-0.025189
11132,20040430,0.120448
11132,20040528,-0.437500
11132,20040630,0.194444
11132,20040730,-0.145116
11132,20040831,-0.084875
11132,20040930,0.136742
11132,20041029,0.105648
11132,20041130,0.121098
11132,20041231,-0.087764
11132,20050131,-0.052729
11132,20050228,0.050781
11132,20050331,-0.072491
11132,20050429,-0.126252
11132,20050531,-0.122706
11132,20050630,0.043137
11132,20050729,0.115288
11132,20050831,0.044944
11132,20050930,0.155914
11132,20051031,-0.161860
11132,20051130,-0.096559
11132,20051230,0.232187
11132,20060131,0.006979
11132,20060228,-0.065347
11132,20060331,0.084746
11132,20060428,-0.027344
11132,20060531,-0.021084
11132,20060630,0.008205
11132,20060731,-0.098678
11132,20060831,-0.094808
11132,20060929,-0.093516
11132,20061031,0.015131
11132,20061130,0.117886
11132,20061229,0.206061
11132,20070131,0.241206
11132,20070228,0.008907
11132,20070330,0.085072
11132,20070430,-0.051036
11132,20070531,0.088075
11132,20070629,0.031519
11132,20070731,0.090972
11132,20070831,0.141948
11132,20070928,0.202341
11132,20071031,0.107093
11132,20071130,-0.212312
11132,20071231,0.039341
11132,20080131,-0.196931
11132,20080229,0.126115
11132,20080331,-0.113122
11132,20080430,0.075255
11132,20080530,0.161329
11132,20080630,-0.038304
11132,20080731,0.096123
11132,20080829,0.063953
11132,20080930,-0.142987
11132,20081031,-0.070669
11132,20081128,-0.181818
11132,20081231,0.309574
11132,20090130,-0.130736
11132,20090227,-0.233886
11132,20090331,0.108974
11132,20090430,0.096821
11132,20090529,0.279315
11132,20090630,0.069516
11132,20090731,-0.256139
11132,20090831,0.018123
11132,20090930,-0.122695
11132,20091030,-0.126087
11132,20091130,0.008292
11132,20091231,0.061678
11132,20100129,-0.013943
11132,20100226,0.099764
11132,20100331,0.109286
11132,20100430,-0.047006
11132,20100528,-0.014865
11132,20100630,0.048011
11132,20100730,0.065445
11132,20100831,-0.140049
11132,20100930,0.067857
11132,20101029,0.003344
11132,20101130,0.076667
11132,20101231,-0.002477
11132,20110131,-0.069832
11132,20110228,0.317317
11132,20110331,-0.029382
11132,20110429,0.064196
11132,20110531,-0.139039
11132,20110630,-0.007690
11132,20110729,-0.043054
11132,20110831,0.022795
11132,20110930,-0.020528
11132,20111031,0.075449
11132,20111130,0.048998
11132,20111230,0.477176
11132,20120131,0.005031
11132,20120229,
;;;;

Accepted Solutions
Solution
‎02-15-2018 01:54 AM
Valued Guide
Posts: 570

Re: Compute cumulative return over future 6 months

Posted in reply to Agent1592

If you have SAS/ETS licensed then Proc Expand can interpolate missing values for you

View solution in original post


All Replies
Solution
‎02-15-2018 01:54 AM
Valued Guide
Posts: 570

Re: Compute cumulative return over future 6 months

Posted in reply to Agent1592

If you have SAS/ETS licensed then Proc Expand can interpolate missing values for you

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 110 views
  • 0 likes
  • 2 in conversation