it coincides with data from 12/31 it seems:
row | date | ticker ret annual_return |
102 | 20121231 | 40076410 | . | . |
103 | 20121231 | 25374820 | . | . |
104 | 20121231 | 44845110 | . | . |
105 | 20121231 | 68402320 | . | . |
106 | 20121231 | 9.8386E34 | . | . |
107 | 20121231 | 37897340 | . | . |
108 | 20131231 | 85812220 | . | . |
109 | 20131231 | 63637520 | . | . |
110 | 20131231 | 42774610 | . | . |
111 | 20131231 | 14163310 | . | . |
112 | 20131231 | 23275110 | . | . |
113 | 20131231 | 23305179 | . | . |
114 | 20131231 | 23305181 | . | . |
115 | 20131231 | 23305182 | . | . |
116 | 20131231 | 26846137 | . | . |
117 | 20131231 | 26846138 | . | . |
118 | 20131231 | 26846139 | . | . |
119 | 20131231 | 26878510 | . | . |
120 | 20131231 | 76276010 | . | . |
121 | 20131231 | 2.504E13 | . | . |
122 | 20131231 | 9.73E12 | . | . |
123 | 20131231 | 1962130 | . | . |
124 | 20131231 | 19063210 | . | . |
125 | 20131231 | 25303110 | . | . |
126 | 20131231 | 24549610 | . | . |
127 | 20131231 | 76129910 | . | . |
128 | 20131231 | 8.9656E14 | . | . |
129 | 20131231 | 92209930 | . | . |
130 | 20131231 | 65651220 | . | . |
131 | 20131231 | 55347740 | . | . |
132 | 20131231 | 45875130 | . | . |
133 | 20131231 | 50345960 | . | . |
134 | 20131231 | 92870310 | . | . |
135 | 20131231 | 8.34E24 | . | . |
136 | 20131231 | 88831430 | . | . |
137 | 20131231 | 3850540 | . | . |
138 | 20131231 | 96823520 | . | . |
139 | 20131231 | 5.5315E14 | . | . |
140 | 20131231 | 40076410 | . | . |
141 | 20131231 | 25374820 | . | . |
142 | 20131231 | 37897340 | . | . |
143 | 20141231 | 85812220 | . | . |
144 | 20141231 | 63637520 | . | . |
145 | 20141231 | 35168080 | . | . |
146 | 20141231 | 22190710 | . | . |
147 | 20141231 | 8.9656E14 | . | . |
148 | 20141231 | 65651220 | . | . |
149 | 20141231 | 45875130 | . | . |
150 | 20141231 | 8.34E24 | . | . |
151 | 20141231 | 88831430 | . | . |
152 | 20141231 | 40076410 | . | . |
153 | 20141231 | 25374820 | . | . |
154 | 20141231 | 74939710 | . | . |
155 | 20151231 | 67068710 | . | . |
156 | 20151231 | 45875130 | . | . |
157 | 20151231 | 8.34E24 | . |
You might have to fix the data first then. If RET is missing, all the calculations based on RET will also be missing.
If that's what needs to be fixed, however, it's easy for SAS to remove the observation with a missing RET and base the calculations on the remaining data.
okay, I will do a datstep to drop observations with missing RET then. Also, I will do some research to see if these annual_return figures are accurate.
The DATA step can be "fixed" if it has to deal with years that don't begin in January or that don't end in December. But it does take a decision as to whether the first year must end in December, and the last year must begin in January and use as many months as appear in the data.
It will lose the years that don't end with December. It's not hard to fix the program (or else you could just add the missing December with RET=0). The other question though is what happens if the first year begins in, say, November. Should the first year then be 2 months long?
Hey @Astounding, thanks for helping me create the variable annual_return! Could I ask what is the source of the equation you used/what it would look like in mathematical form? It's sort of hard to see in the code. Thanks!
I didn't use a formula, just logic. Here's the idea. If you start with $x, and your return is Y% (Y as a decimal fraction), after one time period the value is:
$X * (1 + y)
That compounds monthly. At the end of all the time periods, to compute the total return, you have:
(Final $X - Original $X) / (Original $X)
The math gets simplified when you start with Original $X of 1 each time.
You also wanted the percents on a scale of 0 to 100, instead of 0 to 1. So multiply by 100.
Does an annual return of this magnitude make sense? Like an annual return of 11,819%?
row date ticker cusip ret year annual_return
2 | 20171229 | AA | 1387210 | 0.29776 | 2017 | 11819.254136 |
3 | 20171229 | AABA | 2134610 | -0.002997 | 2017 | 2712.7647923 |
4 | 20141231 | AAC | 30710 | 0.071379 | 2014 | 12518.673092 |
5 | 20151231 | AAC | 30710 | -0.223309 | 2015 | -3079.163211 |
6 | 20161230 | AAC | 30710 | -0.137068 | 2016 | -5451.445011 |
7 | 20171229 | AAC | 30710 | -0.014239 | 2017 | 2296.3094805 |
8 | 20101231 | AAME | 4820910 | 0.068421 | 2010 | 6160.2636444 |
9 | 20111230 | AAME | 4820910 | -0.01005 | 2011 | -304.0471414 |
10 | 20121231 | AAME | 4820910 | 0.064407 | 2012 | 6339.4558727 |
Not at all. What were the monthly values that generated this annual return?
Also remember, these values get multiplied by 100 at the end. So make sure that the result is what you are posting, and not 118.19%
also, I don't recall there being a variable in the dataset for dollars ($) earned over the month. We are only dealing with a rate of return (%) as seen in the ret column. Is this correct?
Dollars earned over the month would be starting value * RET. But since you're not interested in dollars earned, only in the rate of return, the starting value gets arbitrarily set to $1 at the beginning of each year. The final rate of return looks at the final value compared to the starting value. It computes the difference which (based on a starting value of $1) is the rate of return for the year.
Take a simple example: just 2 months in the year, each with a return of 10%. The calculations ...
At the beginning, value is $1.
After 1 month, value is $1 * (1 + 10%) = $1.1
After 2 months, value is $1.1 * (1 + 10%) = $1.21
The combined rate of return compares $1.21 to the original value of $1:
($1.21 - $1) / $1 = 21%
Being interested in the rate of return only, the original starting value does not really matter. Say it was $50 at the beginning.
After 1 month, value is $50 * (1 + 10%) = $55
After 2 months, value is $55 * (1 + 10%) = $60.50
The combined rate of return compares $60.50 to the original value of $50:
($60.50 - $50) / $50 = 21%
Now that I've spelled out these examples, I'll go back and verify that this is the way I applied the formulas.
*************** EDITED:
Yes, the formulas match, with one exception. The calculations in this example yield 0.21 as the annualized rate of return. The problem description looked like you were asking for an answer of 21 instead of 0.21. So the program multiplies by 100 at the end. Multiplying by 100 can easily be removed if you would prefer.
Wow, thank you so much for the help and examples. Very helpful. So if this annual_return variable is accurate, which I believe you when you say it is, then say (for example) firm AA had a good year? An annual return of 11819.25%?
Again, show the monthly returns that appear in the data for AA, for that year. It should be an easily calculation to verify once we're looking at the monthly returns.
One thing to verify, while you are examining the data. Did the prior year for AA have a December date included in the data?
And remember, anything is possible. Some CEOs decide to take a $1 salary for a while, and later go back to a different system.
I don't have time to try the calculation at the moment, but here is the AA data (there appears to only be one year for it):
row | date | ticker | cusip | ret year |
2 | 20170131 | AA | 1387210 | 0.298077 | 2017 |
3 | 20170228 | AA | 1387210 | -0.051029 | 2017 |
4 | 20170331 | AA | 1387210 | -0.005493 | 2017 |
5 | 20170428 | AA | 1387210 | -0.019477 | 2017 |
6 | 20170531 | AA | 1387210 | -0.023421 | 2017 |
7 | 20170630 | AA | 1387210 | -0.008804 | 2017 |
8 | 20170731 | AA | 1387210 | 0.114855 | 2017 |
9 | 20170831 | AA | 1387210 | 0.205494 | 2017 |
10 | 20170929 | AA | 1387210 | 0.062443 | 2017 |
11 | 20171031 | AA | 1387210 | 0.024882 | 2017 |
12 | 20171130 | AA | 1387210 | -0.131226 | 2017 |
13 | 20171229 | AA | 1387210 | 0.29776 | 2017 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.