07-04-2017 10:23 AM - edited 07-07-2017 05:28 AM
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 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.
07-04-2017 10:45 AM
I strongly recommend reading the two papers above to use PROC EXPAND correctly and efficiently.
07-05-2017 01:18 AM - edited 07-05-2017 01:36 AM
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.
07-07-2017 05:15 AM - edited 07-07-2017 05:24 AM
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;