Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Sum of first N rows for each team

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-20-2019 03:49 PM
(1754 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

--

Paige Miller

Paige Miller

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Paige, that worked, additionally i dropped the name, position, points and seq variable as they were not making sense in final output.

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

How to Concatenate Values

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.