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!
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.)
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.
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!
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.
Instructions on how to provide sample data are here:
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.
@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.
|
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
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.
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.)
the date does not always begin with january/end with december. Also, what exactly is a SAS date?
Thanks!
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?
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 |
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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.