Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Analytics
- /
- Forecasting
- /
- Proc Expand to Sum up (*Corrected) previous (monthly) returns

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-04-2017 10:23 AM
(1405 views)

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.

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

SQL can do that .

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.