Proc Expand to Sum up (*Corrected) previous (monthly) returns

[omer2020]

Hi to all. First I am just begginer to SAS and to understand SAS complex macros and terms is very hard at this point. I am replicating a financal Table 1 & Table2 of [Avramov, Doron, and Tarun Chordia, 2006 “Asset Pricing Models and Financial Market Anomalies,”]

To measure momentum, they used monthly accumulated returns of (previous) last 2nd & 3rd (Ret23), 4th to 6th (Ret46), and 7th to 12th (Ret712) months. Data itself is panel data in which I have firms (permno) monthly data for several years along with returns, BM, size and turnover. I have attached "merge1" file which I am using for generating returns.

First I tried to use a simple macro code which take logs, then do sum, and convert back to exp. Everything seems fine with codes but my results have totally different values and trends ( it should be Ret712>Ret46>Ret23) . here are the codes I used initially. By using both option 1 and 2 did'nt improve anything.

proc sort data=merge1 ;	by permno descending date;	run;
%macro calcret;
	data merge2;
	set merge1;
	by permno;
	lret=log(ret+1);	* Take natural logs of the firm return (ret);
	array lagret[12] lagret1-lagret12;
	%do j=1 %to 12;		* Macro "do-loop" to create the 12 lags;
	lagret&j=lag&j(lret);
	%end;

* Calculate the cumulative returns by adding the lags of the returns;
* Option 1;
	ret23=	(exp( sum(of lret lagret2-lagret3)) -1)*100;
	ret46=	(exp( sum(of lret lagret4-lagret6)) -1)*100;
	ret712=	(exp( sum(of lret lagret7-lagret12)) -1)*100;
*Option 2;
	sret23=	((exp( sum(lagret2,lagret3)))-1)*100;
	sret46=	((exp( sum(lagret4,lagret5,lagret6))) -1)*100;
	sret712=((exp( sum(lagret7,lagret8,lagret9,lagret10,lagret11,lagret12)))-1)*100;
	drop l: count i rf;	* Drop unnecessary variables;
%mend calcret;			* End of macro;
%calcret;			* Run the macro;

Now after many attempts and searches, I am planning to try doing the same thing with PROC EXPAND. But I have two issues.

1: Proc Expand backward moving Average/Sum include current and previous values. Is there anyway to accumulate/add/sum only previous values, i.e. starting from t-2 to t-3 (not t to t-3) and accumulated return from t-4 to t-6 (not t to t-6)..

2: Also is it ok to just add (sum up) the return to get accumulated return (which I doubt, bcoz returns are either should be multiplied or converted to natural log before adding. Please give some suggestion)

3: In Proc Expand Chapter on SAS guide, there is this function which seems to do similar thing, as I did above in macro codes.

proc expand data=a out=b from=month to=week;
   id date;
   convert sales / observed=total
                   transformin=(log)
                   transformout=(exp);
run;

Can this code be modified to get summ of  required return by taking (Log) of IN values and then transforming OUT values as (Exp) function?

Lastly, I am not sticking to any particular approach. If you have any other way to calculate accumulated returns, please advise me.

I tried best to explain everything is details. If I missed anything, plz feel free to ask.

Once again thanks alot in advance.

[Reeza]

http://support.sas.com/resources/papers/proceedings10/093-2010.pdf

http://www.lexjansen.com/wuss/2004/tutorials/i_tut_time_series_magic__usi.pdf

I strongly recommend reading the two papers above to use PROC EXPAND correctly and efficiently. 

[omer2020]

thanks alot Reeza. Both articles were very useful and that trim funcion in first article would save me alot.

But unluckily, I could'nt find anything related to what I want to do. I still unable to find a way to having backward moving Sum (MOVESUM) for previous 2nd & 3rd months (excluding current) or for next step, moving average of previous 4th, 5th and 6th month(excluding current and previous 1,2 & 3 months)

Permno Month Ret (Ret23) (Ret46)

10002 1 9                 (-)            (-)

10002 2 10               (-)            (-)

10002 3 6                 (-)            (-)

10002 4 7 (sum of ret of 2 & 1 month) (-)

10002 5 8 (sum of ret of 3 & 2 month) (-)

10002 6 9 (sum of ret of 4 & 3 month) (-)

10002 7 9 (sum of ret of 5 & 4 month) (sum ret of 3, 2 & 1 month)

10002 8 8 (sum of ret of 6 & 5 month) (sum ret of 4, 3 & 2 month)

10002 9 11 (sum of ret of 7 & 6 month) (sum ret of 5, 4 & 3 month)

10002 10 10 (sum of ret of 8 & 7 month) (sum ret of 6, 5 & 4 month)

10002 11 9 (sum of ret of 9 & 8 month) (sum ret of 7, 6 & 5 month)

10002 12 8 (sum of ret of 10 & 9 month) (sum ret of 8, 7 & 6 month)

This is what I am looking to achieve. values of (RET23/RET46) in brackets are what I want. the trim function can help me by inserting missing values if enough values are not availale. but how can i do this proc expand function to run moving Sum for specific numbers of months.

[Ksharp]

SQL can do that .

Post some data and output you want to see ,and let us better understand your question.

[omer2020]

Hi Ksharp, Thank u for ur response. I have used the code given below to get Retun values for specific months, but I do that manually and use proc EXPAND only to generate lagged return values for the required (previous 2nd & 3rd, 4th 5th & 6th, 7th 8th 9th 10th 11th 12th months) periods.

But still I would love to know how it can be done by SQL. I am begginer to SAS and commited to learn more. I would really appriciate if you guide me to do the same in SQL.

I have tried to explain the data and what I want by giving an example above. I am attaching a small sample of my file so that you can understand it better.

CODES FOR ADDING RETUNS OF SPECIFIC PERIODS(MONTHS) BY PROC EXPAND TO GENERATE LAG & MANUAL ADDITION:

*Generating Lagged values by Proc Expand;

proc expand data=merge1 out=lag  method = none;
by permno;
	convert ret = ret;
	convert ret = ret2 / transformout=(lag 2+100);
	convert ret = ret3 / transformout=(lag 3+100);
	convert ret = ret4 / transformout=(lag 4+100);
	convert ret = ret5 / transformout=(lag 5+100);	
	convert ret = ret6 / transformout=(lag 6+100);
	convert ret = ret7 / transformout=(lag 7+100);	
	convert ret = ret8 / transformout=(lag 8+100);
	convert ret = ret9 / transformout=(lag 9+100);
	convert ret = ret10 / transformout=(lag 10+100);
	convert ret = ret11 / transformout=(lag 11+100);
	convert ret = ret12 / transformout=(lag 12+100);

* All lagged generated variables are named as Returns;
 	*so I label them to avoid confusion and to have better;
	*visual understanding of data. it can be skipped;

	label ret2=lag2ret ret3=lag3ret ret4=lag4ret ret5=lag5ret
	ret6=lag6ret ret7=lag7ret ret8=lag8ret ret9=lag9ret ret10=lag10
	ret ret11=lag11ret ret12=lag12ret;
run;

*I removed firms with less than 12 months returns, proc Expand function;
*by using TRIM command can also do that but I am not very good in using it;

data lag2; set lag;
	if ret12 ne .;	
	run;

* My data require me to have at least 36 observation of returns and;
	*firm-level variable, so I use SQL in order to perform that;

proc sql;
	create table merge2 as select * from lag2
	group by permno having count(ret) ge 36
	and count(turnover) ge 36 and 	count(size) ge 36
	and count(bm) 	ge 36 and 	count(ret) ge 36;
	quit;

proc sort data=merge2;	by permno macc;	run;

* I add up returns, but in order to do that I need some extra steps;
* Normal way of adding returns is [ret1 & ret2 (in %);
	*> add 1 (which is 100%) > then multiply ret1 & ret2;
	*> then subtract 1 (which is again 100%) ];
* Since return values are in percent, but unlike excel,;
	*SAS don't treat these values as percent. So I add 100;
	*in PROC EXPAND while generating lagged values to;
	*avoid one extra step > Multiply Retuns values;
	*> Divide the product to 10 power 2n-2 > and then;
	*> subtract it with 100;

data mergexpnd;
set merge2;
ret23 = ((ret2*ret3)/10**2)-100;
ret46 = ((ret4*ret5*ret6)/10**4)-100;
ret712 = ((ret7*ret8*ret9*ret10*ret11*ret12)/10**10)-100;
run;

* Finally I droped lagged values of return after getting required results;

data mergexpnd;
set mergexpnd;
drop ret2 ret3 ret4 ret5 ret6 ret7 ret8 ret9 ret10 ret11 ret12 time;
run;