Calculate Weekly returns

[sparky2121]

The issue: I want to calculate weekly returns as follows: (Price in week 't' + Dividends paid during the week)/Price in Week 't-1'. Basically, this is a simple weekly return. In order to get this, I need to add any dividends paid during the week to end of week's stock price. 

For example, if you focus on permno 10001 and date of 19890911 there is a dividend of $0.0416 paid. The weekly return for the week ending 19890915 should then be calculated as (3 + 0.0416)/3.083 The 3 in the numerator is the price on 19890915. The 3 in the denominator is the price on prior week 19890908. 

The issue is we cannot do this manually. The dataset has has more than 25 million observations. I tried wirting a macro and it works. However, my system crashes because the macro needs to run about 95,000 times and at the rate of 5 seconds per run, it takes more than 130 hours to run.

Any thoughts? If you find a solution (preferably code, if not a step-wise logic and solution in excel will do), I will count this towards one week's assignment score (75 points). If you plan to work on this, as I always say, focus on getting the code right for one company and then we can apply it to others. The dataset with one company is named Temp.  The file with 25 million observations is attached as a zip file in case you want to work on it. 

[andreas_lds]

Please post data in usable form, not all 25 million observations, of course, but enough obs, so that all special cases are included. Also show what you have tried already.

[Patrick]

Very often people in this forum are much more inclined to help you with your assignments if you demonstrate that you've already put in some effort on your own.

First step: Provide some sample data in the form of a fully working SAS data step, explain the logic you need to implement and show us the desired result. Ideally also show us some of the code you've already tried as this will give us a good idea on what level you are at so we can give you answers on your level of SAS coding expertise.

[mkeintz]

As others have pointed out, you need to help us help you.  A start would be to provide sample data, in the form of a working data step, a sample result you expect from that data, and the code you used to get those results.  This will help answer a lot of questions, not the least of which is "what is a week (any seven days? Monday-Sunday?)".

And even in your own terms, please be careful in describing your algorithm.  In particular you wrote:

The weekly return for the week ending 19890915 should then be calculated as (3 + 0.0416)/3.083 The 3 in the numerator is the price on 19890915. The 3 in the denominator is the price on prior week 19890908. 

There is no "3 in the denominator".  Presumably you meant 3.083 as the price on 19890915.  You hope that we will be unambiguous and comprehensive in our responses.   Please return the favor.

Now, as to the use of a macro - there may be no need to create macro code.  You may very well be able to do what you want with data step programming, or possibly a particular PROC available in SAS (maybe PROC EXPAND, but it's too early to say).  But we can provide no advice without being informed clearly about your data, your expected outcome, and your code.

You mention PERMNO, so I assume you are using CRSP daily stock price data.   Probably your dataset is sorted by PERMNO/DATE.  If so, you can quite possibly run a single data step to read in this data, create a series of weekly returns for each PERMNO and go on to your analysis work.  CRSP data (at least the NYSE data) goes back to December 1925, so you may be dealing with enough data that programming efficiency becomes paramount.

BTW very often CRSP daily (that's daily in term of market-trading-days) already has daily return values, one including dividends, and one without.  So you might choose to compound daily returns to get a weekly return, in contrast to the algorithm you suggested..  Given there are at most 5 daily returns per week (well there were actually 6 trading days/week early in the CRSP historical data) you would not suffer from precision issues that can arise from a long series of calculations.

BTW, once you've addressed the efficiency issue. you need to make sure you've addressed stock splits.  But I recommend that issue should be deferred until you have a working program for large datasets.

We look forward to helping once we know what help is actually needed.