turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Rolling average returns

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-03-2017 01:00 PM - edited 06-03-2017 01:00 PM

Hello!

I'm trying to do rolling average returns for each "bemedeciles" (Or groups), from Aprile (t) to Aprile (t+1), Aprile (t+1) to (Aprile t+2) and so on.

The thing that I'm struggling is how to do it. I've search the entire internet, but not found a quite "understandable" methods to do it.

The part where it stops is how to get the rolling average returns in those intervals, based on "Names date", and "bemedecile".

Can someone please help me? I'm on ground zero, no progress on it for 1 day now.

Data:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to TorTheHammer

06-03-2017 01:42 PM

It would help if you could provide have and want datasets in the form of datasteps. From your description it looks like you are only attempting to get averages of each adjacent pair of values, within each group, in your datastep

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to TorTheHammer

06-03-2017 01:56 PM

I agree that there's a lot out there. Here's a post from Rick that lists many of the options:

http://blogs.sas.com/content/iml/2016/01/27/moving-average-in-sas.html

I think you want the second example here - with BY groups:

http://support.sas.com/kb/25/027.html

In general, we can't really help off a picture, for starters we'd have to type it out if we want to work with your data, which many will not do. So a 'generic' question will get 'generic' answers.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to TorTheHammer

06-03-2017 10:41 PM

Here is an example.(if you do not have a big table)

Length of rolling windows is a month forward.

```
data air;
set sashelp.air;
if mod(_n_,100)=1 then group+1;
run;
proc sql;
create table want as
select *,(select mean(air) from air where group=a.group and
date between a.date and intnx('month',a.date,1,'s')) as rolling_mean
from air as a;
quit;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to TorTheHammer

06-04-2017 08:14 AM

You have CRSP monthly data, sorted by PERMNO/DATE, where DATE is the last trading date of the month. You want rolling 12 months average returns (monthly average, or yearly?) (arithmetic average or geometric mean?). Below is geometric mean monthly return for rolling 12-month window.

data want;

do seq=1 by 1 until(last.permno);

set have;

by permno;

log_1plus_ret=log(1+ret);

L12=ifn(seq<=12,0,lag12(log_1plus_ret));

sumlogs=sum(sumlogs,log_1plus_ret)-L12;

if seq<=11 then continue;

geomeanret12=exp(sumlog12/12)-1;

output;

end;

run;

Note that this program assumes there are no "holes" in the monthly series for any permno.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mkeintz

06-04-2017 06:08 PM

First of all, thank you very much for your response!

Yes, I want yearly average returns (From the monthly).

Otherwise, yes I want geometric mean.

Thank you