Learning SAS? Welcome to the exclusive online community for all SAS learners.

Any idea how to combine observations and total?

Occasional Contributor
Posts: 6

Any idea how to combine observations and total?

Hello guys,

I'm having an awful time trying to come up with how a particular code is to be written.

Below is the basis of what I have now:

View image: img1

When that code is run, it looks like this:

View image: img2

As you can see, basically it's a layout of all 2014 world cup games (The image is cropped short, there are actually 64 observations).

What I need to do within a DATA step is combine all distinct team observations (whether they be listed as Team1_Name or Team2_Name) and total their score in a new variable called 'GF'.

For example, in image two 'Spain' is listed in observations 3 and 33. In observation 3, Spain scored 1 goal, in observation 33, Spain scored 3 goals. If those were the only two observations in which Spain appeared, it's output would look like:

Team_Name, GF

Spain, 4

Here is an image I put together using data lines to try and help imagine it (the observations are limited and the goal numbers made up):

View image: img3

Basically it's creating an observation of each country and totaling their goals regardless of whether they are listed as Team1_Name or Team2_Name.

The code probably has to be some complicated combination of IF, THEN, DO, SELECT, WHERE... I have no idea, and I've been banging my head against the wall.

Any suggestions would be greatly appreciated.

Frequent Contributor
Posts: 78

Re: Any idea how to combine observations and total?

Posted in reply to AaronRogers

data fifa;

    length date $10 team1_name $10 team1_score 8 team2_name $10  team2_score 8 ;

    input date team1_name team1_score team2_name  team2_score ;


06/12/2014 Brazil 3 Croatia 1

06/13/2014 Mexico 1 Cameroon 0

03/13/2014 Spain 1 Netherlands 5

06/18/2014 Australia 0 Spain 3



data fifa1 (keep = date team_name team_score) ;

    set fifa (rename = (team1_name = team_name team1_score = team_score));


data fifa2 (keep = date team_name team_score);

    set fifa (rename = (team2_name = team_name team2_score = team_score));



data fifa_all;

    set fifa1 fifa2;


proc sort data = fifa_all; by team_name; run;

data fifa_totals (keep = team_name total);

    set fifa_all;

    retain total 0;

    if first.team_name then total = 0;

    by team_name;

    total = total + team_score;

    if last.team_name then output;


Not applicable
Posts: 1

Re: Any idea how to combine observations and total?

/*Develop two separate file i.e. one consist team_1 and the other team_2*/

data team_1;

set original;

T_n = Team1_name;

T_s = Team1_score;

keep T_n T_s;


data team_2;

set original;

T_n = Team2_name;

T_s = Team2_score;

keep T_n T_s;


/*Concatenate the files*/

data B;

set team_1 team_2;


Proc sort data = B;

by T_N;


/*Final data file*/

proc means data = B;

var T_s; output out = B2  (drop =  _freq_ _type_) sum = GF;

by T_N;


Super User
Super User
Posts: 9,599

Re: Any idea how to combine observations and total?

Posted in reply to AaronRogers

To note, it is easier if you post datasteps with test data.  To get your output:

data have;

  length team_1 team_2 $20;

  team_1="brazil"; team_1_score=1; team_2="croatia"; team_2_score=3; output;

  team_1="croatia"; team_1_score=5; team_2="spain"; team_2_score=1; output;


proc sql;

  create table WANT as

  select  COALESCE(A.TEAM_1,B.TEAM_2) as TEAM,

          sum(A.SCORE,B.SCORE) as SCORE

  from    (select TEAM_1,SUM(TEAM_1_SCORE) as SCORE from HAVE group by TEAM_1) A

  full join (select TEAM_2,SUM(TEAM_2_SCORE) as SCORE from HAVE group by TEAM_2) B

  on      A.TEAM_1=B.TEAM_2;


Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation