BookmarkSubscribeRSS Feed
TorTheHammer
Fluorite | Level 6

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:

sascom.PNG

5 REPLIES 5
art297
Opal | Level 21

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

 

Reeza
Super User

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. 

Ksharp
Super User

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;
mkeintz
PROC Star

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.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
TorTheHammer
Fluorite | Level 6

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 😄 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1311 views
  • 0 likes
  • 5 in conversation