sos.sas

libname frc 'u:\public\frc';
data frc.records(keep=team_id opponent_id result);
  set frc.results;
  array red(1:3) red1-red3;
  array blue(1:3) blue1-blue3;
  select;
    when(redScore > blueScore) result = 2;
    when(redScore < blueScore) result = 0;
    when(redScore = blueScore) result = 1;
  end;
  do r=1 to 3;
    do b=1 to 3;
      team_id = red(r);
      opponent_id = blue(b);
      output;
    end;
  end;
  result = -(result-2);
  do r=1 to 3;
    do b=1 to 3;
      team_id = blue(r);
      opponent_id = red(b);
      output;
    end;
  end;
run;
proc sql;
  create table frc.or as
  select team_id, sum(result) as or
    from frc.records
    group by team_id;
quit;
proc sql;
  create table frc.oor as
  select
    a.team_id, sum(b.or) as oor
    from frc.records a
      inner join frc.or b
        on (a.opponent_id = b.team_id)
    group by a.team_id;
quit;
proc sql;
  create table frc.sos as
  select
    a.team_id, (2*sum(b.or) + sum(c.oor))/3 as sos
    from frc.records a
      inner join frc.or b
        on (a.opponent_id = b.team_id)
      inner join frc.oor c
        on (a.opponent_id = b.team_id)
    group by a.team_id;
quit;