OK, so you want rolling returns for an 11-month or 11-day period, starting 12 periods before the current period through 2 periods before the current period. (I.e. for January 2018, just get the return for January 2017 through November 2017). Or the equivalent in trading days is you're using the CRSP daily data file.
The suggestion below diminishes the amount of lag queues and multiplications. It maintains a variable, GEOMEAN2_12 which is the product of (1+ret12)*(1+ret11)...*(1+ret2) - except you will see I don't create variables ret2, ... ret12. Note the RETAIN statement below tells SAS not to reset GEOMEAN2_12 to missing upon the start of a new observation. It keeps the old value, which conveniently can be modify to produce the new value.
When each new observation is encountered, simply take the preceding geomean2_12 and
- Divide by the oldest (1+ret), namely lag13(1+ret).
- Multiply by the new entry lag2(1+ret).
- Afterwards just make ret2_12 = geomean2_12-1 except for the first 12 records where ret2_12 is left missing.
In other words, the concept (but see below) is:
data want;
set have;
retain geomean2_12 1;
geomean2_12= geomean2_12 * (1+lag2(ret))/(1+lag13(ret));
if lag12(id)=id then ret2_12 = geomean2_12-1;
run;
The problem here is the lag2(ret) and lag13(ret) start out as missing values, making geomean2_12 missing. So even when you get beyond the first 12 observations geomean2_12 is missing, because when it's on both the left and right side of the assignment statement, it stays missing.
Introduce the SUM function. Instead of "1+lag2(ret)" which is missing whenever lag2(ret) is missing use SUM(1,lag2(ret)), which will treat lag2(ret) as a zero even when it is missing, and SUM(1,lag2(ret)) will equal 1.
data want;
set have;
retain geomean2_12 1;
geomean2_12= geomean2_12 * sum(1,lag2(ret)) / sum(1,lag13(ret)) ;
if lag12(id)=id then ret2_12 = geomean2_12-1;
run;
This generates no missing values for geomean2_12, and correct values for ret2_12. And even though the first 12 observations of geomean2_12 for one ID is contaminated by RETs from the prior ID, it doesn't matter since RET2_12 is left as missing until the 13th record of each ID.
However, when there are many ID's, there can be a precision problem for geomean2_12 in the later ID's. You could test this by running the above on your entire file. And then just run it on the last ID in your file. There will be numeric differences due to the fact that computers are digital and of finite precision, meaning the product of some values not be precisely represented in geo_mean2. This can compound over a large file unless geomean2_12 if corrected at some point.
Solution:
- reset geomean2_12 to 1 at the beginning of each ID, and
- also multiply by lag2(1+ret) and divide by lag13(1+ret) only when they are for the same ID.
- The IFN function in the code below (it's like the excel if function) will do this:
data want;
set have;
by id;
retain geomean2_12 1;
if first.id then geomean2_12=1;
geomean2_12= geomean2_12
*sum(1,ifn(lag2(id)=id,lag2(ret),0))
/sum(1,ifn(lag13(id)=id,lag13(ret),0)) ;
if lag12(id)=id then ret2_12 = geomean2_12-1;
run;
The expression "IFN(lag2(id)=id,lag2(ret),0)" does the following:
- Tests whether lag2(id) equals current ID
- If true then return the value of lag2(ret)
- Otherwise return a 0
Same technique is used for the divisor "IFN(lag13(id)=id,lag13(ret),0)"
This avoids compounding numeric imprecision across multiple ID's
I think you will find this faster and easier to maintain. For instance if you want a different historic window size or window offset, you just have to change the 2's and the 13's. No statements to add or remove.
