Running totals across fixed number of observations

[Taasby]

Hi everyone

Doing my master thesis in economics I've ran into some SAS-related problems doing my analysis. Really hope some of you can help me out! 

My data looks like this (subset of it): 

I've created the "score" variable my self:

data Arsenal;
set PL_all;
where HomeTeam = 'Arsenal' or AwayTeam ='Arsenal';

if HomeTeam = 'Arsenal' and FTR = 'H' then Score = 1-1/B365H;
if HomeTeam = 'Arsenal' and FTR = 'D' then Score = -1/B365H;
if HomeTeam = 'Arsenal' and FTR = 'A' then Score = -1/B365H;

if AwayTeam = 'Arsenal' and FTR = 'A' then Score = 1-1/B365A;
if AwayTeam = 'Arsenal' and FTR = 'D' then Score = -1/B365A;
if AwayTeam = 'Arsenal' and FTR = 'H' then Score = -1/B365A;

run;

What I need to do is to obtain a running score for the team (in this case Arsenal). What I need to do is:

- take the sum of obs 1-6 

- take the sum of obs 2-7 

- take the sum of obs 3-8 and so on.

These values should be added to a newvariable called "RunScore".

So I need to keep the number of observations fixed to 6 and then roll through the observations moving one observation down at a time.

This does the trick but only for the first six observations. I tried to do a loop referencing the "i" after the "OBS" and "FIRSTOBS" statements, but it did'nt work.

data Arsenal_1;
set Arsenal (OBS=6 FirstOBS=1) end=eof;
retain RunScore;
if _n_ = 1 then RunScore = Score;
else RunScore = Score + RunScore;
if eof then Prior_Game_7 = RunScore;
run;

Thanks in advance - let me know if you need additional info from me!

Best,

Frederik

[Kurt_Bremser]

Create an array with six elements, indexed from 0 to 5.

Fill that array with

ar{mod(_n_,6)} = score;

and get your running score by

running_score = sum(of ar{*});

For (tested) code, please provide your data in usable form (data step with datalines, posted into a code box).

[Taasby]

Hi Kurt

Thanks for you answer!

I'm not sure how to provide you with my data as datalines - I've imported my data from a csv. file.

Specifically I've read in 10 csv. files - one for each Premier League season and then put them into one data set. 

/* PL 2018/2019 */
filename pldata19 '\\print01\studenter$\Frederik Taasby\Speciale\Data\PL_1819_score.csv';
data work.pltest19;
     infile pldata19 dlm=',' MISSOVER firstobs=2;
	 length Date $22. HomeTeam $22. AwayTeam $22. Referee $22.; 
     input Div $ Date $ HomeTeam $ AwayTeam $ FTHG FTAG FTR $ HTHG HTAG HTR $ Referee $ HS 
           AS HST AST HF AF HC AC HY AY HR AR B365H B365D B365A BWH BWD BWA 
           GBH GBD GBA IWH IWD IWA LBH LBD LBA SBH SBD SBA WHH WHD WHA SJH SJD SJA VCH VCD VCA BSH BSD BSA Bb1X2 BbMxH BbAvH BbMxD BbAvD BbMxA BbAvA BbOU 
           BbMxstoerre_end_to_en_halv BbAvstoerre_end_to_en_halv BbMxmindre_end_to_en_halv BbAvmindre_end_to_en_halv
           BbAH	BbAHh BbMxAHH BbAvAHH BbMxAHA BbAvAHA PSCH PSCD PSCA;
run;

Data PL_all (keep = Date HomeTeam AwayTeam FTHG FTAG FTR B365H B365D B365A);
set pltest11 pltest12 pltest13 pltest14 pltest15 pltest16 pltest17 pltest18 pltest19 pltest20;
run;

data Arsenal;
set PL_all;
where HomeTeam = 'Arsenal' or AwayTeam ='Arsenal';

if HomeTeam = 'Arsenal' and FTR = 'H' then Score = 1-1/B365H;
if HomeTeam = 'Arsenal' and FTR = 'D' then Score = -1/B365H;
if HomeTeam = 'Arsenal' and FTR = 'A' then Score = -1/B365H;

if AwayTeam = 'Arsenal' and FTR = 'A' then Score = 1-1/B365A;
if AwayTeam = 'Arsenal' and FTR = 'D' then Score = -1/B365A;
if AwayTeam = 'Arsenal' and FTR = 'H' then Score = -1/B365A;

run;

I can export the 'Arsenal' dataset into Excel if that is useful?

Frederik

[Kurt_Bremser]

Just open the file with a text editor (Windows Editor should do) and copy/paste the first 30 lines or so into a box opened with the </> button; then I can run your import code against that, and have something to test.

[Taasby]

I see - here you go!

"Date","HomeTeam","AwayTeam","FTHG","FTAG","FTR","B365H","B365D","B365A","Score"
"15/08/10","Liverpool","Arsenal","1","1","D","2.5","3.25","2.88","-0.34722222222222"
"21/08/10","Arsenal","Blackpool","6","0","H","1.17","7","19","0.14529914529914"
"28/08/10","Blackburn","Arsenal","1","2","A","6","4","1.57","0.36305732484076"
"11/09/10","Arsenal","Bolton","4","1","H","1.25","5.5","13","0.2"
"18/09/10","Sunderland","Arsenal","1","1","D","5.5","3.7","1.67","-0.59880239520958"
"25/09/10","Arsenal","West Brom","2","3","A","1.22","6","15","-0.81967213114754"
"03/10/10","Chelsea","Arsenal","2","0","H","1.73","3.6","5","-0.2"
"16/10/10","Arsenal","Birmingham","2","1","H","1.29","5.5","11","0.22480620155038"
"24/10/10","Man City","Arsenal","0","3","A","2.5","3.2","2.9","0.6551724137931"
"30/10/10","Arsenal","West Ham","1","0","H","1.17","7","17","0.14529914529914"
"07/11/10","Arsenal","Newcastle","0","1","A","1.29","5.5","11","-0.77519379844961"
"10/11/10","Wolves","Arsenal","0","2","A","5.5","3.6","1.67","0.40119760479041"
"14/11/10","Everton","Arsenal","1","2","A","3.4","3.3","2.2","0.54545454545454"
"20/11/10","Arsenal","Tottenham","2","3","A","1.62","4","5.5","-0.61728395061728"
"27/11/10","Aston Villa","Arsenal","2","4","A","3.4","3.3","2.2","0.54545454545454"
"04/12/10","Arsenal","Fulham","2","1","H","1.29","5.25","12","0.22480620155038"
"13/12/10","Man United","Arsenal","1","0","H","2","3.4","3.8","-0.26315789473684"
"27/12/10","Arsenal","Chelsea","3","1","H","2.63","3.2","2.75","0.61977186311787"
"29/12/10","Wigan","Arsenal","2","2","D","6.5","4.2","1.5","-0.66666666666666"
"01/01/11","Birmingham","Arsenal","0","3","A","5.5","3.6","1.67","0.40119760479041"
"05/01/11","Arsenal","Man City","0","0","D","1.91","3.6","4","-0.52356020942408"
"15/01/11","West Ham","Arsenal","0","3","A","6","4","1.57","0.36305732484076"
"22/01/11","Arsenal","Wigan","3","0","H","1.2","6.5","15","0.16666666666666"
"01/02/11","Arsenal","Everton","2","1","H","1.4","4.5","8.5","0.28571428571428"
"05/02/11","Newcastle","Arsenal","4","4","D","5","4","1.67","-0.59880239520958"
"12/02/11","Arsenal","Wolves","2","0","H","1.22","6","15","0.18032786885245"
"23/02/11","Arsenal","Stoke","1","0","H","1.3","5.25","10","0.23076923076923"
"05/03/11","Arsenal","Sunderland","0","0","D","1.44","4.33","7.5","-0.69444444444444"
"19/03/11","West Brom","Arsenal","2","2","D","5","3.6","1.73","-0.57803468208092"
"02/04/11","Arsenal","Blackburn","0","0","D","1.25","6","11","-0.8"
"10/04/11","Blackpool","Arsenal","1","3","A","8.5","5","1.36","0.26470588235294"
"17/04/11","Arsenal","Liverpool","1","1","D","1.73","3.6","5","-0.57803468208092"
"20/04/11","Tottenham","Arsenal","3","3","D","2.88","3.4","2.4","-0.41666666666666"
"24/04/11","Bolton","Arsenal","2","1","H","4.5","3.8","1.75","-0.57142857142857"
"01/05/11","Arsenal","Man United","1","0","H","2.5","3.25","2.88","0.6"
"08/05/11","Stoke","Arsenal","3","1","H","5.5","3.75","1.67","-0.59880239520958"
"15/05/11","Arsenal","Aston Villa","1","2","A","1.36","5","8.5","-0.73529411764705"
"22/05/11","Fulham","Arsenal","2","2","D","3.75","3.6","1.95","-0.51282051282051"

Thank you so much!

/Frederik

[Kurt_Bremser · Accepted solution]

See this:

data work.pl_all;
infile datalines dsd dlm=',' truncover firstobs=2;
length Date 4 HomeTeam $22. AwayTeam $22.;
format date yymmdd10.;
input
  Date :ddmmyy10. HomeTeam :$22. AwayTeam :$22. FTHG FTAG FTR :$1.
  B365H B365D B365A Score
;
datalines;
"Date","HomeTeam","AwayTeam","FTHG","FTAG","FTR","B365H","B365D","B365A","Score"
"15/08/10","Liverpool","Arsenal","1","1","D","2.5","3.25","2.88","-0.34722222222222"
"21/08/10","Arsenal","Blackpool","6","0","H","1.17","7","19","0.14529914529914"
"28/08/10","Blackburn","Arsenal","1","2","A","6","4","1.57","0.36305732484076"
"11/09/10","Arsenal","Bolton","4","1","H","1.25","5.5","13","0.2"
"18/09/10","Sunderland","Arsenal","1","1","D","5.5","3.7","1.67","-0.59880239520958"
"25/09/10","Arsenal","West Brom","2","3","A","1.22","6","15","-0.81967213114754"
"03/10/10","Chelsea","Arsenal","2","0","H","1.73","3.6","5","-0.2"
"16/10/10","Arsenal","Birmingham","2","1","H","1.29","5.5","11","0.22480620155038"
"24/10/10","Man City","Arsenal","0","3","A","2.5","3.2","2.9","0.6551724137931"
"30/10/10","Arsenal","West Ham","1","0","H","1.17","7","17","0.14529914529914"
"07/11/10","Arsenal","Newcastle","0","1","A","1.29","5.5","11","-0.77519379844961"
"10/11/10","Wolves","Arsenal","0","2","A","5.5","3.6","1.67","0.40119760479041"
"14/11/10","Everton","Arsenal","1","2","A","3.4","3.3","2.2","0.54545454545454"
"20/11/10","Arsenal","Tottenham","2","3","A","1.62","4","5.5","-0.61728395061728"
"27/11/10","Aston Villa","Arsenal","2","4","A","3.4","3.3","2.2","0.54545454545454"
"04/12/10","Arsenal","Fulham","2","1","H","1.29","5.25","12","0.22480620155038"
"13/12/10","Man United","Arsenal","1","0","H","2","3.4","3.8","-0.26315789473684"
"27/12/10","Arsenal","Chelsea","3","1","H","2.63","3.2","2.75","0.61977186311787"
"29/12/10","Wigan","Arsenal","2","2","D","6.5","4.2","1.5","-0.66666666666666"
"01/01/11","Birmingham","Arsenal","0","3","A","5.5","3.6","1.67","0.40119760479041"
"05/01/11","Arsenal","Man City","0","0","D","1.91","3.6","4","-0.52356020942408"
"15/01/11","West Ham","Arsenal","0","3","A","6","4","1.57","0.36305732484076"
"22/01/11","Arsenal","Wigan","3","0","H","1.2","6.5","15","0.16666666666666"
"01/02/11","Arsenal","Everton","2","1","H","1.4","4.5","8.5","0.28571428571428"
"05/02/11","Newcastle","Arsenal","4","4","D","5","4","1.67","-0.59880239520958"
"12/02/11","Arsenal","Wolves","2","0","H","1.22","6","15","0.18032786885245"
"23/02/11","Arsenal","Stoke","1","0","H","1.3","5.25","10","0.23076923076923"
"05/03/11","Arsenal","Sunderland","0","0","D","1.44","4.33","7.5","-0.69444444444444"
"19/03/11","West Brom","Arsenal","2","2","D","5","3.6","1.73","-0.57803468208092"
"02/04/11","Arsenal","Blackburn","0","0","D","1.25","6","11","-0.8"
"10/04/11","Blackpool","Arsenal","1","3","A","8.5","5","1.36","0.26470588235294"
"17/04/11","Arsenal","Liverpool","1","1","D","1.73","3.6","5","-0.57803468208092"
"20/04/11","Tottenham","Arsenal","3","3","D","2.88","3.4","2.4","-0.41666666666666"
"24/04/11","Bolton","Arsenal","2","1","H","4.5","3.8","1.75","-0.57142857142857"
"01/05/11","Arsenal","Man United","1","0","H","2.5","3.25","2.88","0.6"
"08/05/11","Stoke","Arsenal","3","1","H","5.5","3.75","1.67","-0.59880239520958"
"15/05/11","Arsenal","Aston Villa","1","2","A","1.36","5","8.5","-0.73529411764705"
"22/05/11","Fulham","Arsenal","2","2","D","3.75","3.6","1.95","-0.51282051282051"
;

data Arsenal;
set PL_all;
where HomeTeam = 'Arsenal' or AwayTeam ='Arsenal';
array ar{0:5} _temporary_;
ar{mod(_n_,6)} = score;
run_score = sum(of ar{*});
run;

[Taasby]

Hi Kurt

That WORKED! Thank you so much for your time!

/Frederik