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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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: