I'm working on a football dataset and I want to create an extra column which represent the sum of points of top 11 players for that team.
The sample data for 2 teams looks something like this. The teams are sorted in descending order of points. So I want to create a 5th variable (sum) which represent the sum of top 11 players for 'AFC Bournemouth' and 'Arsenal FC'
The Position column is totally irrelevant here.
I've attached the complete csv dataset for reference.
Name | Team | Position | Points |
Wilson | AFC Bournemouth | FWD | 176.2 |
Fraser | AFC Bournemouth | MID | 174.7 |
King | AFC Bournemouth | FWD | 105.4 |
Brooks | AFC Bournemouth | MID | 104.9 |
Steve Cook | AFC Bournemouth | DEF | 92.1 |
Ake | AFC Bournemouth | DEF | 91 |
Adam Smith | AFC Bournemouth | DEF | 66.4 |
Begovic | AFC Bournemouth | GKP | 65 |
Francis | AFC Bournemouth | DEF | 59.6 |
Lerma | AFC Bournemouth | MID | 58.5 |
Gosling | AFC Bournemouth | MID | 39.1 |
Stanislas | AFC Bournemouth | MID | 38.5 |
Daniels | AFC Bournemouth | DEF | 36 |
Lewis Cook | AFC Bournemouth | MID | 31.6 |
Surman | AFC Bournemouth | MID | 21.8 |
Ibe | AFC Bournemouth | MID | 20.7 |
Rico | AFC Bournemouth | DEF | 14.3 |
Defoe | AFC Bournemouth | FWD | 6.6 |
Mousset | AFC Bournemouth | FWD | 4.3 |
Boruc | AFC Bournemouth | GKP | 0 |
Hyndman | AFC Bournemouth | MID | 0 |
Mings | AFC Bournemouth | DEF | 0 |
Pugh | AFC Bournemouth | MID | 0 |
Simpson | AFC Bournemouth | DEF | 0 |
Lacazette | Arsenal FC | FWD | 152.3 |
Aubameyang | Arsenal FC | FWD | 146.3 |
Ozil | Arsenal FC | MID | 109.8 |
Ramsey | Arsenal FC | MID | 93.9 |
Xhaka | Arsenal FC | MID | 91.8 |
Bellerin | Arsenal FC | DEF | 91.5 |
Mustafi | Arsenal FC | DEF | 81.4 |
Mkhitaryan | Arsenal FC | MID | 77.8 |
Monreal | Arsenal FC | DEF | 68.5 |
Iwobi | Arsenal FC | MID | 64.6 |
Torreira | Arsenal FC | MID | 50.5 |
Cech | Arsenal FC | GKP | 44.4 |
Welbeck | Arsenal FC | FWD | 38.5 |
Holding | Arsenal FC | DEF | 36.4 |
Leno | Arsenal FC | GKP | 27.8 |
Guendouzi | Arsenal FC | MID | 27.4 |
Sokratis | Arsenal FC | DEF | 16.1 |
Kolasinac | Arsenal FC | DEF | 8.7 |
Lichtsteiner | Arsenal FC | DEF | 6.5 |
Maitland-Niles | Arsenal FC | MID | 3.1 |
Elneny | Arsenal FC | MID | 0 |
Koscielny | Arsenal FC | DEF | 0 |
Martinez | Arsenal FC | GKP | 0 |
Mavropanos | Arsenal FC | DEF | 0 |
Medley | Arsenal FC | DEF | 0 |
Nelson | Arsenal FC | MID | 0 |
Nketiah | Arsenal FC | FWD | 0 |
Smith-Rowe | Arsenal FC | MID | 0 |
Thanks in advance.
So assuming you have everything sorted by team and scoring (seems like you do)
data want;
set have;
by team;
if first.team then do;
seq=0;
sum_points=0;
end;
seq+1;
if seq<=11 then sum_points+points;
if seq>11 then sum_points=.;
run;
So assuming you have everything sorted by team and scoring (seems like you do)
data want;
set have;
by team;
if first.team then do;
seq=0;
sum_points=0;
end;
seq+1;
if seq<=11 then sum_points+points;
if seq>11 then sum_points=.;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.