topic How to express to the power of 1/11 in SAS Programming

How to express to the power of 1/11

Songchan — Mon, 29 Oct 2018 22:27:21 GMT

Hello guys,

Do you know how to express to the power of 1/11? I tried a lot of ways but didn't work.

ret12_2m = ((1+ret12)*(1+ret11)*(1+ret10)*(1+ret9)*(1+ret8)*(1+ret7)*(1+ret6)*(1+ret5)*(1+ret4)*(1+ret3)*(1+ret2))**(1/11)-1;

Thank you

Re: How to express to the power of 1/11

PGStats — Mon, 29 Oct 2018 22:37:33 GMT

x**(1/11) is OK. What problem did you meet? Please post the SAS log.

Re: How to express to the power of 1/11

LaurieF — Mon, 29 Oct 2018 22:39:01 GMT

Is it just your parentheses?

ret12_2m = (((1+ret12)*(1+ret11)*(1+ret10)*(1+ret9)*(1+ret8)*(1+ret7)*(1+ret6)*(1+ret5)*(1+ret4)*(1+ret3)*(1+ret2))**(1/11))-1;

Re: How to express to the power of 1/11

mkeintz — Tue, 30 Oct 2018 14:03:53 GMT

Am I right that you want a constant single-period rate of return which compounds to an actual 11-period return? Presumably your data are in a time series (likely 1 record per month). If so, you don't have to constantly add or multiply 11 periods. Instead maintain a variable, call it LOGPLUS1 which has the 11-period sum of log(1+ret).

Then, with each incoming record, add log(1+ret) of the period entering the time span, and subtract log(1+ret) of the period leaving the time span. Afterwards (1) divide by 11, (2) exponentiate, and (3) subtract 1 for the desired value. This can be conveniently done with the lag function.

Let's say you want the 11-month return rate for 1 month back through 11 months back. (i.e. for the December record, you want the monthly rate for Jan (11 month lag) through Nov (1 month lag). Then for the subsequent January you want to take away the previous January (12 month lag) and add the immediately preceding December (1 month lag):

data want;
  set have ;
  retain logplus1 0;
  logplus1=sum(logplus1,lag1(log(1+ret)),-1*sum(0,lag12(log(1+ret))));
  if _n_>11 then x= exp(logplus1/11)-1;
run;

The reason for "-1*sum(0,lag12(log12(1+ret)))" instead of "-lag12(log(1+ret))" is to avoid errors due to attempting to subtract a missing value, which would generate a missing value for logplus1. But "-sum(x,0)" will always generate a non-missing value, even if x is missing.

But if your data is sorted by id and month, then you have to avoid contaminating one id with returns from the prior id. So only add lag1 and subtract lag12 when the corresponding ID's match:

data want;
  set have ;
  by id;
  retain logplus1 0;
  if first.id then logplus1=0;
  logplus1=sum(logplus1
               ,ifn(id=lag1(id),lag1(log(1+ret)),0)
               ,ifn(id=lag12(id),-1*sum(0,lag12(log12(1+ret))),0)
               );
  if lag11(id)=id then x= exp(logplus1/11)-1;
run;

Re: How to express to the power of 1/11

Ksharp — Tue, 30 Oct 2018 13:03:24 GMT

It seems you want get a portfolio's expect return by its geometric mean.

Check function GEOMEAN(argument<,argument,…>)

x3=geomean(of x1-x2);

Re: How to express to the power of 1/11

Songchan — Tue, 30 Oct 2018 17:41:13 GMT

Thank you for replying.

I am trying to calculate compounded returns of prior 12 to 2 month, so my whole procedure is like this,

data temp01; set mylib.crsp1969b; 
	ret2=lag2(ret); ret3=lag3(ret);
	ret4=lag4(ret); ret5=lag5(ret); ret6=lag6(ret);
	ret7=lag7(ret); ret8=lag8(ret); ret9=lag9(ret);
	ret10=lag10(ret); ret11=lag11(ret); ret12=lag12(ret);

	perm2=lag2(permno); perm3=lag3(permno);perm4=lag4(permno); 
    perm5=lag5(permno); perm6=lag6(permno);perm7=lag7(permno);
	perm8=lag8(permno); perm9=lag9(permno);perm10=lag10(permno);
	perm11=lag11(permno);perm12=lag12(permno);

	if permno^=perm2 then ret2=.;
	if permno^=perm3 then ret3=.;
	if permno^=perm4 then ret4=.;
	if permno^=perm5 then ret5=.;
	if permno^=perm6 then ret6=.;
    if permno^=perm7 then ret7=.;
	if permno^=perm8 then ret8=.;
	if permno^=perm9 then ret9=.;
	if permno^=perm10 then ret10=.;
	if permno^=perm11 then ret11=.;
	if permno^=perm12 then ret12=.;
	if permno=perm12 then 
		ret12_2m =((1+ret12)*(1+ret11)*(1+ret10)*(1+ret9)*(1+ret8)*(1+ret7)*
                   (1+ret6)*(1+ret5)*(1+ret4)*(1+ret3)*(1+ret2))**(1/11)-1;
run;

Re: How to express to the power of 1/11

mkeintz — Wed, 31 Oct 2018 18:34:28 GMT

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.