BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sastuck
Pyrite | Level 9

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!

 Screen Shot 2018-03-04 at 4.48.00 PM.png

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

 

View solution in original post

32 REPLIES 32
MarkWik
Quartz | Level 8

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. 

sastuck
Pyrite | Level 9

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!

Reeza
Super User
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?
sastuck
Pyrite | Level 9

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.

Reeza
Super User

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.


 

MarkWik
Quartz | Level 8

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

sastuck
Pyrite | Level 9
 
   dateticker return
220100226EGAS-0.000656 
320100331EGAS0.020643 
420100430EGAS0.124385 
520100528EGAS0.004829 
620100630EGAS-0.043421 
720100730EGAS0.083333 
820100831EGAS-0.111263 
920100930EGAS0.07656 
1020101029EGAS0.032815 
1120101130EGAS-0.118436 
1220101231EGAS0.052291 
1320110131EGAS0.028992 
1420110228EGAS0.022727 
1520110331EGAS0.072404 
1620110429EGAS-0.038789 
1720110531EGAS0.02805 
1820110630EGAS0.008261 
1920110729EGAS-0.028139 
2020110831EGAS-0.013864 
2120110930EGAS0.005009 
2220111031EGAS0.005005 
2320111130EGAS-0.005 
2420111230EGAS0.051835 
2520120131EGAS-0.019702 
2620120229EGAS0.005005 
2720120330EGAS0.04876 
2820120430EGAS-0.015 
2920120531EGAS-0.084361 
3020120629EGAS-0.027325 
3120120731EGAS0.009406 
3220120831EGAS-0.012315 
3320120928EGAS0.000501 
3420121031EGAS0.012676 
3520121130EGAS-0.045504 
3620121231EGAS-0.015231 
3720130131EGAS0.062701 
3820130228EGAS0.011651 
3920130328EGAS0.030684 
4020130430EGAS0.017157 
4120130531EGAS-0.000484 
4220130628EGAS0.006323 
4320130731EGAS0.016019 
4420130830EGAS-0.016795 
4520130930EGAS0.004412 
4620131031EGAS-0.018137 
4720131129EGAS-0.134905 
4820131231EGAS-0.058858 
4920140131EGAS0.147572 
5020140228EGAS0.025627 
5120140331EGAS0.079594 
5220140430EGAS0.087972 
5320140530EGAS-0.033486 
5420140630EGAS0.009056 
5520140731EGAS0.22723 
5620140829EGAS-0.121412 
5720140930EGAS0.050975 
5820141031EGAS-0.03514 
5920141128EGAS0.017181 
6020141231EGAS-0.037826 
6120150130EGAS-0.115245 
6220150227EGAS0.035897 
6320150331EGAS-0.000495 
6420150430EGAS0.011044 
6520150529EGAS-0.006951 
6620150630EGAS0.03 
6720150731EGAS-0.013107 
6820150831EGAS-0.111665 
6920150930EGAS0.003367 
7020151030EGAS0.001678 
7120151130EGAS-0.00907 
7220151231EGAS-0.132151 
7320160129EGAS0.116779 
7420160229EGAS-0.055288 
7520160331EGAS-0.006361 
7620160429EGAS-0.055698 
7720160531EGAS-0.021918 
7820160630EGAS-0.021008 
7920160729EGAS0.026466 
8020160831EGAS0.03662 
8120160930EGAS0.04212 
8220161031EGAS0.619948 
8320161130EGAS0.012146 
8420161230EGAS0.01 
8520170131EGAS0.007968 
8620170228EGAS0 
8720170331EGAS0.009881 
8820170428EGAS-0.015748 
8920170531EGAS0.016 
9020170630EGAS0.023622 
9120170731EGAS0.001934 
9220170831EGAS. 
9320100129BTFG0.365854 
9420100226BTFG0.155612 
9520100331BTFG0.07064 
9620100430BTFG0.276289 
9720100528BTFG-0.28433 
9820100630BTFG-0.164786 
9920100730BTFG-0.17027 
10020100831BTFG0.009772 
 
 
                              
sastuck
Pyrite | Level 9

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 

Reeza
Super User

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.

Astounding
PROC Star

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

 

sastuck
Pyrite | Level 9

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

 

Thanks!

Astounding
PROC Star

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?

sastuck
Pyrite | Level 9

@Astounding

 

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

 

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

 

rowdate    ticker       cusipmonthly return  annual return
19020121231AAN253530-0.0146346.2307808656
19120131231AAN2535300.0265364.226821326
19220141231AAN2535300.0771674.2817206905
19320151231AAN253530-0.077462-26.52987081
19420161230AAN2535300.09950243.452160793
19520171229AAN2535300.05726424.946061871
19620101231AAON360200.09980546.882111929
19720111230AAON36020-0.0648117.6652957857
19820121231AAON36020-0.0085513.6715155717
19920131231AAON360200.038349131.07860291
20020141231AAON360200.0805985.9833021736
Astounding
PROC Star

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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