cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Taasby
  • 50 Posts
  • 1 Likes Given
  • 0 Solutions
  • 0 Likes Received
This widget could not be displayed.

Re: Rolling window retaing fixed number of observations along the way

by Calcite | Level 5 in SAS Programming
‎10-15-2020 04:16 AM
‎10-15-2020 04:16 AM
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 ... View more

Re: Rolling window retaing fixed number of observations along the way

by Calcite | Level 5 in SAS Programming
‎10-15-2020 03:51 AM
‎10-15-2020 03:51 AM
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 ... View more

Rolling window retaing fixed number of observations along the way

by Calcite | Level 5 in SAS Programming
‎10-14-2020 07:17 AM
‎10-14-2020 07:17 AM
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 ... View more

Re: Running summation on fixed number of observations

by Calcite | Level 5 in SAS Programming
‎10-14-2020 07:10 AM
‎10-14-2020 07:10 AM
Figured this out my self - running the code before moving any observations:   proc sort data=avg; by date; run; 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; /Frederik ... View more

Re: Running summation on fixed number of observations

by Calcite | Level 5 in SAS Programming
‎10-14-2020 06:03 AM
‎10-14-2020 06:03 AM
Cheers Kurt!   This bit of code did for me:  data avg_2; do _n_=1 by 1 until(last.hometeam); set avg; by HomeTeam; if _n_ > 18 then output; end; run; And thanks for the suggestion on streamlining my code!   The only thing that is left for me to get into the table is the sum and average across all teams for the past 19 games. So when Arsenal have played 19 games and scored 36 goals how many goals are scored in total for all teams for last 19 games (a total of 380 games) in the same timeperiod? This is tricky as the data is now ordered by HomeTeam and I want to cut it by Date. I aim to get something like the last two rows of this table:                       I need this info to obtain relative measures of the teams. For instance here Arsenal would have a "home attacking strength" of 1,89/1,57 = 1.20. This will be for a later step.   /Frederik       ... View more

Re: Running summation on fixed number of observations

by Calcite | Level 5 in SAS Programming
‎10-14-2020 05:49 AM
‎10-14-2020 05:49 AM
I think i solved it myself using this code:   data avg_2; do _n_=1 by 1 until(last.hometeam); set avg; by HomeTeam; if _n_ > 18 then output; end; run; /Frederik ... View more

Re: Running summation on fixed number of observations

by Calcite | Level 5 in SAS Programming
‎10-14-2020 05:13 AM
‎10-14-2020 05:13 AM
Hi Kurt   Got it! THANKS! I am almost there now! This is my code: data avg; set PL_Poisson_1; by HomeTeam; array window{0:18} _temporary_; if first.team then do; do count = 0 to 18; window{count} = .; end; count = 1; end; else count + 1; window{mod(count,19)} = FTHG; Goals_For = sum(of window{*}); Avg_Goals_For = sum(of window{*}) / min(count,19); drop count; array window1{0:18} _temporary_; if first.team then do; do count = 0 to 18; window1{count} = .; end; count = 1; end; else count + 1; window1{mod(count,19)} = FTAG; Goals_Against = sum(of window1{*}); Avg_Goals_Against = sum(of window1{*}) / min(count,19); drop count; run; Which gives me output like this:   However when there is no more observations for Arsenal and the HomeTeam variable takes on the next value (this would be Aston Villa) the window (up until observation number 19 for Aston Villa) will include some info on Arsenal. Is there someway to restrict the array to restart when the HomeTeam takes on a new value?    Best, Frederik ... View more

Re: Running summation on fixed number of observations

by Calcite | Level 5 in SAS Programming
‎10-13-2020 10:06 AM
‎10-13-2020 10:06 AM
Now I got how it was constructed! That is a very neat way to do it!   My code (with my variables): data avg; set PL_Poisson_1; by HomeTeam; array window{0:18} _temporary_; if first.team then do; do count = 0 to 18; window{count} = .; end; count = 1; end; else count + 1; window{mod(count,19)} = FTHG; scr_avg_home = sum(of window{*}) / min(count,19); drop count; run; So I am rolling the window on the "FTHG" (Finish Time Home Goals) variable. So now the "Score_avg" variable contains the average of the Homegoals for the 19 last homegames - that is great!   Is it possible within the same bit of code to make a "score_avg" variable in the same manner but for the goals conceded - that would be for the "FTAG" (Finish Time Away Goals) variable?   Now my output looks like this - and if possible I would like another column to the right (another variable) on the goals conceded..   Thanks   /Frederik ... View more

Re: Running summation on fixed number of observations

by Calcite | Level 5 in SAS Programming
‎10-13-2020 09:34 AM
‎10-13-2020 09:34 AM
Hi Kurt   Running this code I get missing values for score and score_avg. The error messages are that these variables fails to initialise...   data avg; set PL_Poisson_1; by HomeTeam; array window{0:18} _temporary_; if first.team then do; do count = 0 to 18; window{count} = .; end; count = 1; end; else count + 1; window{mod(count,19)} = score; score_avg = sum(of window{*}) / min(count,19); drop count; run; /Frederik ... View more

Re: Running summation on fixed number of observations

by Calcite | Level 5 in SAS Programming
‎10-13-2020 08:34 AM
‎10-13-2020 08:34 AM
Hi Kurt   Thanks for your time once again!   Observing a given game - say Arsenal vs. Liverpool - I aim to have the goals-info on the previous 19 homegames for Arsenal and the previous 19 away games for Liverpool in a manner like the tables above. I shall use these tables to get a sense of the relative strengths of the teams before the game:   This I will then (by the Poisson distribution) use to obtain probabilities of different scorelines like this:   Best, Frederik ... View more

Re: Remove fixed number of observations when variable takes a given va...

by Calcite | Level 5 in SAS Programming
‎10-13-2020 08:11 AM
‎10-13-2020 08:11 AM
Cheers Richard!    I got the answer from another suggestion but your code worked perfectly fine as well! So thank you very much!   /Frederik ... View more

Re: Remove fixed number of observations when variable takes a given va...

by Calcite | Level 5 in SAS Programming
‎10-13-2020 08:08 AM
‎10-13-2020 08:08 AM
That worked! Thank you so much! /Frederik ... View more

Re: Remove fixed number of observations when variable takes a given va...

by Calcite | Level 5 in SAS Programming
‎10-13-2020 07:55 AM
‎10-13-2020 07:55 AM
Cheers Amir!   Doesn't quite do the job unfortunately even though the code makes sense to me.. I get the following message and a dataset of 0 observations..     Best, Frederik ... View more

Remove fixed number of observations when variable takes a given value

by Calcite | Level 5 in SAS Programming
‎10-13-2020 07:35 AM
‎10-13-2020 07:35 AM
Hi everyone   I got 3800 observations on Premier League games for the last 10 seasons. For each season I want to delete the first 60 observations. To elaborate - for season = 2011 I want to delete the first 60 obs (by Date).  - for season = 2012 I want to delete the first 60 obs (by Date). - etc.   I've tried the following code - but this only works for the 2011-season... Proberbly because this is the first season and thus the first 60 obs. of dataset contains info on this season..   data merged_file_3200; set merged_file_3800; if _n_ le 60 and season=2011 then delete; run;   My data looks like this (a subset of it):     "HomeTeam","AwayTeam","Season","Date","FTHG","FTAG","FTR","B365H","B365D","B365A","B365H_True","B365D_True","B365A_True","Score","run_score","score_before_game7","HomeTeam_score_before_game7","AwayTeam_score_before_game7" "Aston_Villa","West_Ham","2011","08/14/2010","3","0","H","2","3.3","4","2.07329843","3.50442478","4.30434783","-0.23232323232323","-0.23232323232323",".","0","0" "Blackburn","Everton","2011","08/14/2010","1","0","H","2.88","3.25","2.5","3.04027718","3.45557470","2.61989177","-0.38169515669515","-0.38169515669515",".","0","0" "Bolton","Fulham","2011","08/14/2010","0","0","D","2.2","3.3","3.4","2.28668478","3.49896050","3.61158798","-0.27688651218062","-0.27688651218062",".","0","0" "Chelsea","West_Brom","2011","08/14/2010","6","0","H","1.17","7","17","1.19630531","8.06040139","24.98145933","-0.04002968708851","-0.04002968708851",".","0","0" "Sunderland","Birmingham","2011","08/14/2010","2","2","D","2.1","3.3","3.6","2.18726986","3.52074513","3.86431227","-0.45719095719095","-0.45719095719095",".","0","0" "Tottenham","Man_City","2011","08/14/2010","0","0","D","2.4","3.3","3","2.50632911","3.50442478","3.16800000","-0.39898989898989","-0.39898989898989",".","0","0" "Wigan","Blackpool","2011","08/14/2010","0","4","A","1.83","3.5","4.5","1.89279264","3.73711507","4.89970251","-0.52831988897562","-0.52831988897562",".","0","0" "Wolves","Stoke","2011","08/14/2010","2","1","H","2.3","3.25","3.2","2.40120462","3.45581507","3.39933681","0.58354236343366","0.58354236343366",".","0","0" "Liverpool","Arsenal","2011","08/15/2010","1","1","D","2.5","3.25","2.88","2.61989177","3.45557470","3.04027718","-0.38169515669515","-0.38169515669515",".","0","0" "Man_United","Newcastle","2011","08/16/2010","3","0","H","1.25","5.5","15","1.27577320","6.03658537","19.80000000","-0.05050505050505","-0.05050505050505",".","0","0" "Arsenal","Blackpool","2011","08/21/2010","6","0","H","1.17","7","19","1.19335873","7.92849864","27.85381862","-0.03590172011224","0.76000425612159","0.79590597623384","-0.32891737891737","0.79590597623384" "Birmingham","Blackburn","2011","08/21/2010","2","1","H","2.2","3.25","3.4","2.29483952","3.46131941","3.63197211","-0.27533251062662","0.39575011045599","0.67108262108262","-0.25877825877825","0.67108262108262" "Everton","Wolves","2011","08/21/2010","1","1","D","1.5","4","7","1.54601227","4.34482759","8.12903226","-0.12301587301587","0.46052649041779","0.58354236343366","-0.38169515669515","0.58354236343366" "Stoke","Tottenham","2011","08/21/2010","1","2","A","3.4","3.3","2.2","3.61158798","3.49896050","2.28668478","0.56268568033273","0.16369578134284","-0.39898989898989","-0.29417502787068","-0.39898989898989" "West_Brom","Sunderland","2011","08/21/2010","1","0","H","2.5","3.25","2.88","2.61989177","3.45557470","3.04027718","0.61830484330484","0.57827515621633","-0.04002968708851","-0.04002968708851","-0.45719095719095" "West_Ham","Bolton","2011","08/21/2010","1","3","A","2.25","3.3","3.25","2.34711246","3.51319381","3.45658013","-0.42605542605542","-0.65837865837865","-0.23232323232323","-0.23232323232323","-0.43731431966726" "Wigan","Chelsea","2011","08/21/2010","0","6","A","15","6","1.22","20.40892193","6.71149144","1.24687713","-0.04899817850637","-0.577318067482","-0.52831988897562","-0.52831988897562","0.16409298762239" "Fulham","Man_United","2011","08/22/2010","2","2","D","6","4","1.57","6.72057075","4.30792683","1.61531866","-0.6190728945506","-0.40291127838898","0.21616161616161","-0.27688651218062","0.21616161616161" "Newcastle","Aston_Villa","2011","08/22/2010","6","0","H","3.2","3.25","2.3","3.39933681","3.45581507","2.40120462","0.70582497212932","0.65531992162426","-0.05050505050505","-0.05050505050505","0.51767676767676" "Man_City","Liverpool","2011","08/23/2010","3","0","H","2.38","3.25","3.1","2.47920939","3.43785992","3.27046441","0.59664560613327","0.2809890404767","-0.31565656565656","-0.31565656565656","-0.38169515669515" "Blackburn","Arsenal","2011","08/28/2010","1","2","A","6","4","1.57","6.72057075","4.30792683","1.61531866","-0.14879688605803","0.24695322439796","0.39575011045599","0.39575011045599","-0.16688837478311" "Blackpool","Fulham","2011","08/28/2010","2","2","D","3.2","3.25","2.3","3.39933681","3.45581507","2.40120462","-0.41645763656633","-0.84214103480499","-0.42568339823866","0.76000425612159","-0.42568339823866" "Chelsea","Stoke","2011","08/28/2010","2","0","H","1.11","9","23","1.13326757","10.79746241","40.02961672","-0.02498150324237","-0.59604304329368","-0.57106154005131","0.36208934463515","-0.57106154005131" "Man_United","West_Ham","2011","08/28/2010","3","0","H","1.14","8","19","1.16425532","9.36986301","29.10638298","-0.03435672514619","-0.69273538352485","-0.65837865837865","-0.40291127838898","-0.65837865837865" "Tottenham","Wigan","2011","08/28/2010","0","1","A","1.25","5.75","13","1.27705011","6.37073864","16.67286245","0.94002229654403","0.36270422906203","-0.577318067482","0.16369578134284","-0.577318067482" "Wolves","Newcastle","2011","08/28/2010","1","1","D","2.4","3.2","3.1","2.50364554","3.38694992","3.27512839","-0.39941756272401","0.06110892769378","0.46052649041779","0.46052649041779","0.65531992162426" "Aston_Villa","Everton","2011","08/29/2010","1","0","H","2.5","3.25","2.88","2.61989177","3.45557470","3.04027718","-0.32891737891737","-1.35743793243793","-1.02852055352055","0.10121913111043","-1.02852055352055" "Bolton","Birmingham","2011","08/29/2010","2","2","D","2.1","3.3","3.6","2.18726986","3.52074513","3.86431227","-0.45719095719095","-0.1838085661615","0.27338239102944","0.27338239102944","0.30546142310848" "Liverpool","West_Brom","2011","08/29/2010","1","0","H","1.22","6","15","1.24687713","6.71149144","20.40892193","-0.04899817850637","0.52927697770995","0.57827515621633","-0.68746212849628","0.57827515621633"   Any help is much appreciated!   Best, Frederik     ... View more

Re: Running summation on fixed number of observations

by Calcite | Level 5 in SAS Programming
‎10-13-2020 07:25 AM
‎10-13-2020 07:25 AM
Hi Kurt   Thanks for the advice - I think it would make sense to actually create such variables!    Let me try to elaborate on what I am aiming to do: I got 3.800 observations on Premier League matches for the last 10 seasons. Due to relegation and promotion the data contains info on 36 different teams which have played at least 19 home games and at most 190 home games. For each team I want a rolling window of 19 homegames including info on goals scored and goals conceded AND the sum and average of goals scored and goals conceded in that particular window. I then want to do the exact same thing for away games. This should give me the following two tabels (for 1 home-window and 1 away-window):   From here I will create a third table multiplying some of the numbers for each row in the two tables above - I guess I will cross that bridge when I get there. For now I am trying to make SAS get me tables like the above.   /Frederik   ... View more
Contact Me
Likes given to
View All
Quick links