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
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
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
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
do you have any example data?
Bart
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
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
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
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
Exactly right Bart!
Thank you so much!
/Frederik
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
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
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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: