Sum of first N rows for each team

lawilap — Wed, 20 Mar 2019 19:49:00 GMT

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.

Re: Sum of first N rows for each team

PaigeMiller — Wed, 20 Mar 2019 20:25:40 GMT

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;

Re: Sum of first N rows for each team

lawilap — Wed, 20 Mar 2019 23:21:39 GMT

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

topic Re: Sum of first N rows for each team in SAS Programming

Sum of first N rows for each team

Re: Sum of first N rows for each team

Re: Sum of first N rows for each team