2 weeks ago
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!
2 weeks ago  last edited 2 weeks ago
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.)
2 weeks ago
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.
2 weeks ago
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!
2 weeks ago
2 weeks ago
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.
2 weeks ago
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.
2 weeks ago
@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.
2 weeks ago

2 weeks ago
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
2 weeks ago
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.
2 weeks ago  last edited 2 weeks ago
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.)
2 weeks ago
the date does not always begin with january/end with december. Also, what exactly is a SAS date?
Thanks!
2 weeks ago  last edited 2 weeks ago
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?
2 weeks ago
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 
2 weeks ago  last edited 2 weeks ago
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.