03042018 04:49 PM
Good afternoon,
In my firm performance dataset I have monthly return figures for firms from 20102017. 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!
03052018 02:33 AM  edited 03052018 02:36 AM
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.)
03042018 04:58 PM
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.
03042018 05:04 PM
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!
03042018 05:04 PM
03042018 05:07 PM
I need to substitute the decimal form of the firm's return for the onemonth periods into the following formula: [((1 + R)^12)  1] x 100 , using a negative number for a negative monthly return.
03042018 05:35 PM
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 onemonth periods into the following formula: [((1 + R)^12)  1] x 100 , using a negative number for a negative monthly return.
03042018 06:23 PM
@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.
03042018 06:31 PM

03042018 06:33 PM
I apologize if this is hard to work withI 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 20102017, so there should be 7 rows per firm ideally
03042018 07:59 PM
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.
03052018 02:33 AM  edited 03052018 02:36 AM
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.)
03052018 06:16 PM
the date does not always begin with january/end with december. Also, what exactly is a SAS date?
Thanks!
03052018 06:30 PM  edited 03052018 06:35 PM
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?
03052018 06:27 PM
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 
03052018 06:33 PM  edited 03052018 06:34 PM
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?
Need further help from the community? Please ask a new question.