## Need new variable for annualized returns

Solved
Regular Contributor
Posts: 184

# Need new variable for annualized returns

Good afternoon,

In my firm performance dataset I have monthly return figures for firms from 2010-2017. I would like to have a new variable that is annual return. Could you help me create a datastep which I can use for the whole dataset to show annualized returns for each firm? That way I could match it to CEO salaries in my other dataset. I will attach a screenshot of what the data look like. Any help is super appreciated!

Accepted Solutions
Solution
‎03-05-2018 06:47 PM
Super User
Posts: 6,754

## Re: Need new variable for annualized returns

[ Edited ]

Part of the answer depends on the data.

Do you always begin with January and end with December?

Is DATE a SAS date, or merely a numeric value with YMD?

Is your data in sorted order by TICKER DATE?

Assuming "yes", "just a number", and "yes", you could program it in this way:

data temp;

set have;

by ticker date;

retain annual_return 1;

annual_return = annual_return * (1 + RET);

month = int( mod(date,10000) / 100);

if month = 12;

annual_return = (annual_return - 1) * 100;

output;

annual_return = 1;

run;

If DATE is actually a SAS date, the calculation of MONTH is easier:

month = month(date);

Note that the annualized return is calculated separately for each year.  If you get 10% for one year, and 20% for the next year, the total value after two years would be 1.1 * 1.2 * original value ... all assuming that I got the formulas correct.  So check that the results look reasonable (I can't test this right now.)

All Replies
Frequent Contributor
Posts: 103

## Re: Need new variable for annualized returns

Can you please post your sample data and expected output in the form of a datastep or at least plain text. I don;t think many would be interested in converting a screen shot to a real dataset.

Regular Contributor
Posts: 184

## Re: Need new variable for annualized returns

what is the best way to post my sample data? Also what do you mean by expected output in the form of a data step?

thanks for the help!

Super User
Posts: 23,683

## Re: Need new variable for annualized returns

You also need to explain how the 'annualized return' would be calculated, ie can you just take the last obs and first obs for each year or do you need to consider all lines of data?
Regular Contributor
Posts: 184

## Re: Need new variable for annualized returns

I need to substitute the decimal form of the firm's return for the one-month periods into the following formula: [((1 + R)^12) - 1] x 100 , using a negative number for a negative monthly return.

Super User
Posts: 23,683

## Re: Need new variable for annualized returns

Instructions on how to provide sample data are here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

By expected output, what would you expect as results from the sample data you've posted. Ideally, they align, so the answers in the output match up with the sample data you provided so someone can test their code.

sastuck wrote:

I need to substitute the decimal form of the firm's return for the one-month periods into the following formula: [((1 + R)^12) - 1] x 100 , using a negative number for a negative monthly return.

Frequent Contributor
Posts: 103

## Re: Need new variable for annualized returns

@sastuck  At the very least,you could copy paste the sample from excel as text and not as images. The expected output the same thing, meaning the results that you expect to be populated in the output dataset derived using your sample.

Regular Contributor
Posts: 184

## Re: Need new variable for annualized returns

 date ticker return
 2 20100226 EGAS -0.000656
 3 20100331 EGAS 0.020643
 4 20100430 EGAS 0.124385
 5 20100528 EGAS 0.004829
 6 20100630 EGAS -0.043421
 7 20100730 EGAS 0.083333
 8 20100831 EGAS -0.111263
 9 20100930 EGAS 0.07656
 10 20101029 EGAS 0.032815
 11 20101130 EGAS -0.118436
 12 20101231 EGAS 0.052291
 13 20110131 EGAS 0.028992
 14 20110228 EGAS 0.022727
 15 20110331 EGAS 0.072404
 16 20110429 EGAS -0.038789
 17 20110531 EGAS 0.02805
 18 20110630 EGAS 0.008261
 19 20110729 EGAS -0.028139
 20 20110831 EGAS -0.013864
 21 20110930 EGAS 0.005009
 22 20111031 EGAS 0.005005
 23 20111130 EGAS -0.005
 24 20111230 EGAS 0.051835
 25 20120131 EGAS -0.019702
 26 20120229 EGAS 0.005005
 27 20120330 EGAS 0.04876
 28 20120430 EGAS -0.015
 29 20120531 EGAS -0.084361
 30 20120629 EGAS -0.027325
 31 20120731 EGAS 0.009406
 32 20120831 EGAS -0.012315
 33 20120928 EGAS 0.000501
 34 20121031 EGAS 0.012676
 35 20121130 EGAS -0.045504
 36 20121231 EGAS -0.015231
 37 20130131 EGAS 0.062701
 38 20130228 EGAS 0.011651
 39 20130328 EGAS 0.030684
 40 20130430 EGAS 0.017157
 41 20130531 EGAS -0.000484
 42 20130628 EGAS 0.006323
 43 20130731 EGAS 0.016019
 44 20130830 EGAS -0.016795
 45 20130930 EGAS 0.004412
 46 20131031 EGAS -0.018137
 47 20131129 EGAS -0.134905
 48 20131231 EGAS -0.058858
 49 20140131 EGAS 0.147572
 50 20140228 EGAS 0.025627
 51 20140331 EGAS 0.079594
 52 20140430 EGAS 0.087972
 53 20140530 EGAS -0.033486
 54 20140630 EGAS 0.009056
 55 20140731 EGAS 0.22723
 56 20140829 EGAS -0.121412
 57 20140930 EGAS 0.050975
 58 20141031 EGAS -0.03514
 59 20141128 EGAS 0.017181
 60 20141231 EGAS -0.037826
 61 20150130 EGAS -0.115245
 62 20150227 EGAS 0.035897
 63 20150331 EGAS -0.000495
 64 20150430 EGAS 0.011044
 65 20150529 EGAS -0.006951
 66 20150630 EGAS 0.03
 67 20150731 EGAS -0.013107
 68 20150831 EGAS -0.111665
 69 20150930 EGAS 0.003367
 70 20151030 EGAS 0.001678
 71 20151130 EGAS -0.00907
 72 20151231 EGAS -0.132151
 73 20160129 EGAS 0.116779
 74 20160229 EGAS -0.055288
 75 20160331 EGAS -0.006361
 76 20160429 EGAS -0.055698
 77 20160531 EGAS -0.021918
 78 20160630 EGAS -0.021008
 79 20160729 EGAS 0.026466
 80 20160831 EGAS 0.03662
 81 20160930 EGAS 0.04212
 82 20161031 EGAS 0.619948
 83 20161130 EGAS 0.012146
 84 20161230 EGAS 0.01
 85 20170131 EGAS 0.007968
 86 20170228 EGAS 0
 87 20170331 EGAS 0.009881
 88 20170428 EGAS -0.015748
 89 20170531 EGAS 0.016
 90 20170630 EGAS 0.023622
 91 20170731 EGAS 0.001934
 92 20170831 EGAS .
 93 20100129 BTFG 0.365854
 94 20100226 BTFG 0.155612
 95 20100331 BTFG 0.07064
 96 20100430 BTFG 0.276289
 97 20100528 BTFG -0.28433
 98 20100630 BTFG -0.164786
 99 20100730 BTFG -0.17027
 100 20100831 BTFG 0.009772

Regular Contributor
Posts: 184

## Re: Need new variable for annualized returns

I apologize if this is hard to work with--I am in my first year of using SAS. You'll see in the data I copy and pasted that I have monthly return data. I would like to create a new variable which is one figure for each month. So annualized return per firm. This dataset shows 2010-2017, so there should be 7 rows per firm ideally

Super User
Posts: 23,683

## Re: Need new variable for annualized returns

Ok...instead of that please right click the data set and view in Excel. Select just the records you want to share with us and then save as a csv file and upload that.

The data you pasted above has a lot of line feeds and extra information.

You can either attache the CSV data or insert it directly using the notepad icon, 6/7th icon on the Rich Text editor here.

{ i } icon.

That will insert it as text, not html.

Solution
‎03-05-2018 06:47 PM
Super User
Posts: 6,754

## Re: Need new variable for annualized returns

[ Edited ]

Part of the answer depends on the data.

Do you always begin with January and end with December?

Is DATE a SAS date, or merely a numeric value with YMD?

Is your data in sorted order by TICKER DATE?

Assuming "yes", "just a number", and "yes", you could program it in this way:

data temp;

set have;

by ticker date;

retain annual_return 1;

annual_return = annual_return * (1 + RET);

month = int( mod(date,10000) / 100);

if month = 12;

annual_return = (annual_return - 1) * 100;

output;

annual_return = 1;

run;

If DATE is actually a SAS date, the calculation of MONTH is easier:

month = month(date);

Note that the annualized return is calculated separately for each year.  If you get 10% for one year, and 20% for the next year, the total value after two years would be 1.1 * 1.2 * original value ... all assuming that I got the formulas correct.  So check that the results look reasonable (I can't test this right now.)

Regular Contributor
Posts: 184

## Re: Need new variable for annualized returns

the date does not always begin with january/end with december. Also, what exactly is a SAS date?

Thanks!

Super User
Posts: 6,754

## Re: Need new variable for annualized returns

[ Edited ]

SAS expects to store dates as integers.  Arbitrarily, 0 is equivalent to January 1, 1960.  You can see this for yourself:

data test;

do x=0 to 10;

output;

end;

run;

proc print data=test;

format x date9.;

run;

proc print data=test;

format x yymmdd10.;

run;

One question about what you want before I go changing the program ...

If the first observation for a ticker is in April, do you want the first observation in the output to run April through December, or do you want it to run April through the following March?

Regular Contributor
Posts: 184

## Re: Need new variable for annualized returns

@Astounding

With your suggestion, this is what I ended up with.

There are many missing observations, but it appears to have worked?

 row date ticker cusip monthly return  annual return
 190 20121231 AAN 253530 -0.014634 6.23078
 191 20131231 AAN 253530 0.026536 4.22682
 192 20141231 AAN 253530 0.077167 4.28172
 193 20151231 AAN 253530 -0.077462 -26.5299
 194 20161230 AAN 253530 0.099502 43.4522
 195 20171229 AAN 253530 0.057264 24.9461
 196 20101231 AAON 36020 0.099805 46.8821
 197 20111230 AAON 36020 -0.064811 7.6653
 198 20121231 AAON 36020 -0.008551 3.67152
 199 20131231 AAON 36020 0.038349 131.079
 200 20141231 AAON 36020 0.080598 5.9833
Super User
Posts: 6,754

## Re: Need new variable for annualized returns

[ Edited ]

Does "missing observations" coincide with data that ends before December?

If you were to look at the monthly numbers for AAON, do the annual numbers look right?

☑ This topic is solved.

Discussion stats
• 32 replies
• 336 views
• 5 likes
• 4 in conversation