Rolling window retaing fixed number of observations along the way

[Taasby]

Hi everyone

I'm using this array (thanks to Kurt for helping me out with this):

data avg_1;
set avg;
by Date;

array window{0:379} _temporary_;
array window1{0:379} _temporary_;
if first.hometeam
then do;
  do count = 0 to 379;
    window{count} = .;
    window1{count} = .;
  end;
  count = 1;
end;
else count + 1;
window{mod(count,380)} = FTHG;
Goals_For_Total = sum(of window{*});
Avg_Goals_For_Total = sum(of window{*}) / min(count,380);

window1{mod(count,380)} = FTAG;
Goals_Against_Total = sum(of window1{*});
Avg_Goals_Against_Total = sum(of window1{*}) / min(count,380);
drop count;

run;

What this does is taking the sum of obs 1-380, then 2-381, 3-381 etc. This is very close to what I am aiming to do, but not quite. I want to

- take the sum of obs 1-380, then retain this sum for the next 10 observations

- take the sum of obs 11-391, then retain this sum for the next 10 observations

- etc.

How do I modify my array to do this?

Best, Frederik

[yabwon]

Hi,

how about:

if mod(count,10) = 1 then
  do;
    Goals_For_Total = sum(of window{*});
  end;
Avg_Goals_For_Total = sum(of window{*}) / min(count,380);

?

Bart

[Taasby]

Hi 

Thanks for the suggestion! Plugging it into my code I now have (I'm going for 200 obs at a time instead of 380 obs):

data Away_Table_1;
set Away_Table;
by Date;

array window{0:199} _temporary_;
array window1{0:199} _temporary_;
if first.awayteam
then do;
  do count = 0 to 199;
    window{count} = .;
    window1{count} = .;
  end;
  count = 1;
end;
else count + 1;

window{mod(count,200)} = FTAG;
if mod(count,10) = 1 
then do;
    Goals_For_Total = sum(of window{*});
  end;
Avg_Goals_For_Total = sum(of window{*}) / min(count,200);

window1{mod(count,200)} = FTHG;
if mod(count,10) = 1 
then do;
    Goals_Against_Total = sum(of window{*});
  end;
Avg_Goals_Against_Total = sum(of window{*}) / min(count,200);
drop count;

run;

It doesn't quite seem to work though - I get a lot of "."s for the Goals_Total variables. What am I doing wrong?

/Frederik

[yabwon]

do you have any example data?

Bart

[Taasby]

Yes - here:

"HomeTeam","AwayTeam","Season","Date","FTHG","FTAG","FTR","Goals_For","Avg_Goals_For","Goals_Against","Avg_Goals_Against"
"Sunderland","Birmingham","2011","08/14/2010","2","2","D","10","1","24","2.4"
"Wigan","Blackpool","2011","08/14/2010","0","4","A","16","1.6","10","1"
"Blackburn","Everton","2011","08/14/2010","1","0","H","15","1.5","13","1.3"
"Bolton","Fulham","2011","08/14/2010","0","0","D","12","1.2","15","1.5"
"Tottenham","Man City","2011","08/14/2010","0","0","D","18","1.8","7","0.7"
"Wolves","Stoke","2011","08/14/2010","2","1","H","10","1","12","1.2"
"Chelsea","West Brom","2011","08/14/2010","6","0","H","10","1","20","2"
"Aston Villa","West Ham","2011","08/14/2010","3","0","H","6","0.6","26","2.6"
"Liverpool","Arsenal","2011","08/15/2010","1","1","D","18","1.8","14","1.4"
"Man United","Newcastle","2011","08/16/2010","3","0","H","5","0.5","37","3.7"
"Birmingham","Blackburn","2011","08/21/2010","2","1","H","4","0.8","16","3.2"
"Arsenal","Blackpool","2011","08/21/2010","6","0","H","0","0","6","6"
"West Ham","Bolton","2011","08/21/2010","1","3","A","11","1.1","19","1.9"
"Wigan","Chelsea","2011","08/21/2010","0","6","A","19","1.9","11","1.1"
"West Brom","Sunderland","2011","08/21/2010","1","0","H","6","0.6","19","1.9"
"Stoke","Tottenham","2011","08/21/2010","1","2","A","18","1.8","14","1.4"
"Everton","Wolves","2011","08/21/2010","1","1","D","6","0.6","15","1.5"
"Newcastle","Aston Villa","2011","08/22/2010","6","0","H","5","0.5","33","3.3"
"Fulham","Man United","2011","08/22/2010","2","2","D","10","1","14","1.4"
"Man City","Liverpool","2011","08/23/2010","3","0","H","14","1.4","18","1.8"
"Blackburn","Arsenal","2011","08/28/2010","1","2","A","18","2","6","0.66666666666666"
"Blackpool","Fulham","2011","08/28/2010","2","2","D","12","1.2","17","1.7"
"Wolves","Newcastle","2011","08/28/2010","1","1","D","12","1.2","17","1.7"
"Chelsea","Stoke","2011","08/28/2010","2","0","H","9","0.9","17","1.7"
"Man United","West Ham","2011","08/28/2010","3","0","H","6","0.6","20","2"
"Tottenham","Wigan","2011","08/28/2010","0","1","A","13","1.3","12","1.2"
"Bolton","Birmingham","2011","08/29/2010","2","2","D","6","1.2","6","1.2"
"Aston Villa","Everton","2011","08/29/2010","1","0","H","6","0.6","20","2"
"Sunderland","Man City","2011","08/29/2010","1","0","H","13","1.3","9","0.9"
"Liverpool","West Brom","2011","08/29/2010","1","0","H","8","0.8","10","1"
"Man City","Blackburn","2011","09/11/2010","1","1","D","9","0.9","17","1.7"
"Newcastle","Blackpool","2011","09/11/2010","0","2","A","13","1.3","24","2.4"
"Arsenal","Bolton","2011","09/11/2010","4","1","H","1","1","4","4"
"West Ham","Chelsea","2011","09/11/2010","1","3","A","18","1.8","12","1.2"
"Everton","Man United","2011","09/11/2010","3","3","D","21","2.1","10","1"
"Wigan","Sunderland","2011","09/11/2010","1","1","D","7","0.7","10","1"
"West Brom","Tottenham","2011","09/11/2010","1","1","D","18","1.8","10","1"
"Fulham","Wolves","2011","09/11/2010","2","1","H","11","1.1","16","1.6"
"Birmingham","Liverpool","2011","09/12/2010","0","0","D","17","1.7","6","0.6"
"Stoke","Aston Villa","2011","09/13/2010","2","1","H","10","1","23","2.3"
"Sunderland","Arsenal","2011","09/18/2010","1","1","D","11","1.1","11","1.1"
"West Brom","Birmingham","2011","09/18/2010","3","1","H","9","0.9","24","2.4"
"Aston Villa","Bolton","2011","09/18/2010","1","1","D","2","0.66666666666666","8","2.66666666666666"
"Blackburn","Fulham","2011","09/18/2010","1","1","D","13","1.3","16","1.6"
"Everton","Newcastle","2011","09/18/2010","0","1","A","9","0.9","14","1.4"
"Stoke","West Ham","2011","09/18/2010","1","1","D","10","1","9","0.9"
"Tottenham","Wolves","2011","09/18/2010","3","1","H","15","1.5","20","2"
"Chelsea","Blackpool","2011","09/19/2010","4","0","H","6","1","19","3.16666666666666"
"Man United","Liverpool","2011","09/19/2010","3","2","H","12","1.2","26","2.6"
"Wigan","Man City","2011","09/19/2010","0","2","A","28","2.8","7","0.7"
"Blackpool","Blackburn","2011","09/25/2010","1","2","A","6","1","17","2.83333333333333"
"Man City","Chelsea","2011","09/25/2010","1","0","H","13","1.3","18","1.8"
"Fulham","Everton","2011","09/25/2010","0","0","D","4","0.4","6","0.6"
"Liverpool","Sunderland","2011","09/25/2010","2","2","D","15","1.5","14","1.4"
"West Ham","Tottenham","2011","09/25/2010","1","0","H","13","1.3","9","0.9"
"Arsenal","West Brom","2011","09/25/2010","2","3","A","3","3","2","2"
"Birmingham","Wigan","2011","09/25/2010","0","0","D","7","1","11","1.57142857142857"
"Wolves","Aston Villa","2011","09/26/2010","1","2","A","9","0.9","9","0.9"
"Bolton","Man United","2011","09/26/2010","2","2","D","20","2","9","0.9"
"Newcastle","Stoke","2011","09/26/2010","1","2","A","10","1","22","2.2"

Really appreciate it!!

/Frederik

[yabwon]

And this version:

data Away_Table;
  call streaminit(123);
  format date yymmdd10.;
  do date = today() to today() + 3;
    do _N_ =  1 to 300;
      FTAG=RAND('INTEGER',100);
      FTHG=RAND('INTEGER',100);
      output;
    end;
  end;
run;

data Away_Table_1;
  set Away_Table;
  by Date;

  array window{0:199} _temporary_;
  array window1{0:199} _temporary_;
  if first.awayteam
  then do;
    call missing(of window{*}, of window1{*});
    count = 1;
  end;
  else count + 1;

  retain Goals_For_Total Avg_Goals_For_Total Goals_Against_Total Avg_Goals_Against_Total 0;

  window{mod(count,200)} = FTAG;
  if mod(count,10) = 1 
  then do;
      Goals_For_Total = sum(of window{*});
    end;
  Avg_Goals_For_Total = sum(of window{*}) / min(count,200);

  window1{mod(count,200)} = FTHG;
  if mod(count,10) = 1 
  then do;
      Goals_Against_Total = sum(of window1{*});
    end;
  Avg_Goals_Against_Total = sum(of window1{*}) / min(count,200);
  drop count;
run;

All the best

Bart

[Taasby]

Cheers Bart!

Amazing piece of code - but not 100% what I'm aiming for I think. The code as it is now seem to retain 10 values from the very first observations. I want it to "start" retaining the value for the first time after 200 observations: I want to sum the first 200 observations (1-200) then retain this sum for the next 10 observations (200-210), then sum 11-211 and retain this sum for the next 10 observations (211-221), etc. Does it make sense?

Thank you!!

/Frederik

[yabwon · Accepted solution]

Do you mean something like this:

data Away_Table;
  call streaminit(123);
  format date yymmdd10.;
  do date = today() to today() + 3;
    do _N_ =  1 to 300;
      FTAG=RAND('INTEGER',100);
      FTHG=RAND('INTEGER',100);
      output;
    end;
  end;
run;


data Away_Table_1;
  set Away_Table;
  by Date;

  array window{0:199} _temporary_;
  array window1{0:199} _temporary_;
  if first.Date
  then do;
    call missing(of window{*}, of window1{*});
    count = 1;
  end;
  else count + 1;

  retain Goals_For_Total Avg_Goals_For_Total Goals_Against_Total Avg_Goals_Against_Total 0;

  window{mod(count,200)} = FTAG;
  if count< 200 or mod(count,10) = 1 
  then do;
      Goals_For_Total = sum(of window{*});
    end;
  Avg_Goals_For_Total = sum(of window{*}) / min(count,200);

  window1{mod(count,200)} = FTHG;
  if count< 200 or mod(count,10) = 1  
  then do;
      Goals_Against_Total = sum(of window1{*});
    end;
  Avg_Goals_Against_Total = sum(of window1{*}) / min(count,200);
  drop count;
run;

?

All the best

Bart

[Taasby]

Exactly right Bart!

Thank you so much!

/Frederik

[yabwon]

one thing surprised me:

set Away_Table;
by Date;

array window{0:199} _temporary_;
array window1{0:199} _temporary_;
if first.awayteam

you are grouping by Date but you are using first.awayteam, should it be that way?

Bart

[Taasby]

I agree that it looks a bit off but the code - as it is now - does what I want to. And when changing it to "first.date" it behaves it an unintended manner. But thanks for pointing it out anyway!!

/Frederik 

[Kurt_Bremser]

Your mistake of using a non-existing first. variable causes this conditional block to never execute, so you miss the initialization on a group change, and carry the values over to the next group.

[Taasby]

Hi Kurt

That makes sense! In that bid of code I really do not want it to cut it by anything - and as you've now cleared up it really doesn't, so I guess it all makes sense! What it does - and what I want it to do (at least up until i figured out that I want to modify it slightly, hence this thread) - is to sum 200 observations at a time moving one observation at a time not "resetting" at any time. 

/Frederik