topic Need new variable for annualized returns in SAS Studio

Need new variable for annualized returns

sastuck — Sun, 04 Mar 2018 21:49:10 GMT

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!

Re: Need new variable for annualized returns

MarkWik — Sun, 04 Mar 2018 21:58:07 GMT

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.

Re: Need new variable for annualized returns

Reeza — Sun, 04 Mar 2018 22:04:17 GMT

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?

Re: Need new variable for annualized returns

sastuck — Sun, 04 Mar 2018 22:04:46 GMT

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!

Re: Need new variable for annualized returns

sastuck — Sun, 04 Mar 2018 22:07:38 GMT

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.

Re: Need new variable for annualized returns

Reeza — Sun, 04 Mar 2018 22:35:47 GMT

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-data-AKA-generate/ta-p/258712

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.

Re: Need new variable for annualized returns

MarkWik — Sun, 04 Mar 2018 23:23:00 GMT

@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.

Re: Need new variable for annualized returns

sastuck — Sun, 04 Mar 2018 23:31:04 GMT

date

ticker

return

20100226

EGAS

-0.000656

20100331

EGAS

0.020643

20100430

EGAS

0.124385

20100528

EGAS

0.004829

20100630

EGAS

-0.043421

20100730

EGAS

0.083333

20100831

EGAS

-0.111263

20100930

EGAS

0.07656

20101029

EGAS

0.032815

20101130

EGAS

-0.118436

20101231

EGAS

0.052291

20110131

EGAS

0.028992

20110228

EGAS

0.022727

20110331

EGAS

0.072404

20110429

EGAS

-0.038789

20110531

EGAS

0.02805

20110630

EGAS

0.008261

20110729

EGAS

-0.028139

20110831

EGAS

-0.013864

20110930

EGAS

0.005009

20111031

EGAS

0.005005

20111130

EGAS

-0.005

20111230

EGAS

0.051835

20120131

EGAS

-0.019702

20120229

EGAS

0.005005

20120330

EGAS

0.04876

20120430

EGAS

-0.015

20120531

EGAS

-0.084361

20120629

EGAS

-0.027325

20120731

EGAS

0.009406

20120831

EGAS

-0.012315

20120928

EGAS

0.000501

20121031

EGAS

0.012676

20121130

EGAS

-0.045504

20121231

EGAS

-0.015231

20130131

EGAS

0.062701

20130228

EGAS

0.011651

20130328

EGAS

0.030684

20130430

EGAS

0.017157

20130531

EGAS

-0.000484

20130628

EGAS

0.006323

20130731

EGAS

0.016019

20130830

EGAS

-0.016795

20130930

EGAS

0.004412

20131031

EGAS

-0.018137

20131129

EGAS

-0.134905

20131231

EGAS

-0.058858

20140131

EGAS

0.147572

20140228

EGAS

0.025627

20140331

EGAS

0.079594

20140430

EGAS

0.087972

20140530

EGAS

-0.033486

20140630

EGAS

0.009056

20140731

EGAS

0.22723

20140829

EGAS

-0.121412

20140930

EGAS

0.050975

20141031

EGAS

-0.03514

20141128

EGAS

0.017181

20141231

EGAS

-0.037826

20150130

EGAS

-0.115245

20150227

EGAS

0.035897

20150331

EGAS

-0.000495

20150430

EGAS

0.011044

20150529

EGAS

-0.006951

20150630

EGAS

0.03

20150731

EGAS

-0.013107

20150831

EGAS

-0.111665

20150930

EGAS

0.003367

20151030

EGAS

0.001678

20151130

EGAS

-0.00907

20151231

EGAS

-0.132151

20160129

EGAS

0.116779

20160229

EGAS

-0.055288

20160331

EGAS

-0.006361

20160429

EGAS

-0.055698

20160531

EGAS

-0.021918

20160630

EGAS

-0.021008

20160729

EGAS

0.026466

20160831

EGAS

0.03662

20160930

EGAS

0.04212

20161031

EGAS

0.619948

20161130

EGAS

0.012146

20161230

EGAS

0.01

20170131

EGAS

0.007968

20170228

EGAS

20170331

EGAS

0.009881

20170428

EGAS

-0.015748

20170531

EGAS

0.016

20170630

EGAS

0.023622

20170731

EGAS

0.001934

20170831

EGAS

20100129

BTFG

0.365854

20100226

BTFG

0.155612

20100331

BTFG

0.07064

20100430

BTFG

0.276289

20100528

BTFG

-0.28433

20100630

BTFG

-0.164786

20100730

BTFG

-0.17027

100

20100831

BTFG

0.009772

Re: Need new variable for annualized returns

sastuck — Sun, 04 Mar 2018 23:33:02 GMT

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

Re: Need new variable for annualized returns

Reeza — Mon, 05 Mar 2018 00:59:58 GMT

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.

Re: Need new variable for annualized returns

Astounding — Mon, 05 Mar 2018 07:36:30 GMT

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.)

Re: Need new variable for annualized returns

sastuck — Mon, 05 Mar 2018 23:16:53 GMT

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

Thanks!

Re: Need new variable for annualized returns

sastuck — Mon, 05 Mar 2018 23:27:20 GMT

@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.2307808656

191

20131231

AAN

253530

0.026536

4.226821326

192

20141231

AAN

253530

0.077167

4.2817206905

193

20151231

AAN

253530

-0.077462

-26.52987081

194

20161230

AAN

253530

0.099502

43.452160793

195

20171229

AAN

253530

0.057264

24.946061871

196

20101231

AAON

36020

0.099805

46.882111929

197

20111230

AAON

36020

-0.064811

7.6652957857

198

20121231

AAON

36020

-0.008551

3.6715155717

199

20131231

AAON

36020

0.038349

131.07860291

200

20141231

AAON

36020

0.080598

5.9833021736

Re: Need new variable for annualized returns

Astounding — Mon, 05 Mar 2018 23:35:32 GMT

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?

Re: Need new variable for annualized returns

Astounding — Mon, 05 Mar 2018 23:34:27 GMT

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?

Re: Need new variable for annualized returns

sastuck — Mon, 05 Mar 2018 23:36:39 GMT

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

Re: Need new variable for annualized returns

Astounding — Mon, 05 Mar 2018 23:42:10 GMT

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.

Re: Need new variable for annualized returns

sastuck — Mon, 05 Mar 2018 23:44:30 GMT

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.

Re: Need new variable for annualized returns

Astounding — Tue, 06 Mar 2018 00:38:04 GMT

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.

Re: Need new variable for annualized returns

sastuck — Tue, 06 Mar 2018 00:40:51 GMT

so if it is fixed, then the data set will lose everything that doesn’t fit that parameter (starting with jan and ending with dec)?