topic Re: Running totals across fixed number of observations in SAS Programming

Running totals across fixed number of observations

Taasby — Thu, 08 Oct 2020 08:04:07 GMT

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

Re: Running totals across fixed number of observations

Kurt_Bremser — Thu, 08 Oct 2020 08:47:57 GMT

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).

Re: Running totals across fixed number of observations

Taasby — Thu, 08 Oct 2020 09:00:52 GMT

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

Re: Running totals across fixed number of observations

Kurt_Bremser — Thu, 08 Oct 2020 09:39:24 GMT

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.

Re: Running totals across fixed number of observations

Taasby — Thu, 08 Oct 2020 10:03:09 GMT

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

Re: Running totals across fixed number of observations

Kurt_Bremser — Thu, 08 Oct 2020 10:28:17 GMT

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;

Re: Running totals across fixed number of observations

Taasby — Thu, 08 Oct 2020 10:48:38 GMT

Hi Kurt

That WORKED! Thank you so much for your time!

/Frederik