Creating rolling 30-day returns variable

[gergely_batho]

In this code there are 2 pointers on the data: the current and the 30 day-ahead. This way you can maintain a moving sum, and output when you read from the current pointer.

Probably you want to delete obs where moving_cnt is too small (<=20). No problem if a day is missing.

proc sort data=have;

by Firm Date;

run;

%let timeWindow=30;/*day*/

%let timePeriod=day;

data have_exp / view=have_exp;

  set have;

  date=intnx("&timePeriod.",Date,-&timeWindow.,'s');

run;

data want;

  set have(in=inT) have_exp ;/*interleaving original and expiry datasets*/

  by Firm Date;

  if first.Firm then do;/*initializing counters and sums at beginning of a group*/

  moving_cnt=0;

  moving_sum=.;

  moving_Ret=1;retain moving_Ret;

  end;

  if inT then do;/*if transaction, then first output than calculate*/

  output;

  moving_cnt+(-1);

  moving_sum+(-Ret);

  moving_Ret=moving_Ret/(1+Ret);

  end;

  else do;/*if expiry, then calculate*/

  moving_cnt+1;

  moving_sum+Ret;

  moving_Ret=moving_Ret*(1+Ret);

  end;

run;

[mahler_ji]

Hello all, Thank you very much for all of your help.  I was able to get this to work, finally!

and , thank you very much, and I was able to use a combination of what you gave to me to make this work. 

By the way, I am using SAS 9.2.

@muthia, I am going to email you privately about understanding arrays, as that is something that I really need to understand better!

Thanks so much!

John