SAS query

[scb]

DATA ;
INPUT ID $ Team $ Rank $ Point
DATALINES;
111 T1 A1 0

112 T1 A2 300

113 T1 A2 600

114 T1 A3 400

115 T1 A3 100

116 T2 A1 0

117 T2 A2 1000

118 T2 A2 1600

119 T2 A3 1400

120 T2 A3 1100

;
run;

May I know how to get the TPoints? Rank A1 Tpoints will be from all A2 + A3 within the team.  A2 T Points will  be from A3 within the team; A3 Tpoints will be his own pts.

ID  Team  Rank  Ponts  TPoints

111 T1     A1       0         1400

112 T1     A2     300       500 

113 T1    A2      600       500

114 T1    A3     400        400

115 T1    A3    100         100

116 T2    A1    0              5100

117 T2    A2    1000        2500

118 T2    A2    1600        2500

119 T2    A3    1400        1400  

120 T2    A3   1100         1100

Thanks.

[Tom]

Does this do what you want?

proc sql;
  create table want as 
  select a.id, a.team, a.rank, a.points
       , case when (a.rank='A3') then a.points
              else sum( b.points )
         end as Tpoints
  from have a 
  left join have b
  on a.team = b.team and a.rank < b.rank
  group by a.id, a.team, a.rank, a.points 
  order by a.id
  ;
quit;

[art297]

And here is an approach using a datastep:

DATA have;
  INPUT ID $ Team $ Rank $ Point;
  DATALINES;
111 T1 A1 0
112 T1 A2 300
113 T1 A2 600
114 T1 A3 400
115 T1 A3 100
116 T2 A1 0
117 T2 A2 1000
118 T2 A2 1600
119 T2 A3 1400
120 T2 A3 1100
;
run;

data want (drop=Tot:);
  do until (last.Team);
    set have;
    by Team;
    if First.Team then do;
      TPoints=0;
      Total1=0;
      Total2=0;
    end;
    TPoints+Point;
    if Rank eq 'A1' then Total1+Point;
    else if Rank eq 'A2' then Total2+Point;
  end;
  do until (last.Team);
    set have;
    by Team Rank;
    if first.Rank then do;
      if Rank eq 'A1' then TPoints+(Total1*-1);
      else if Rank eq 'A2' then TPoints+(Total2*-1);
    end;
    if Rank eq 'A3' then TPoints=Point;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

[Ksharp]

DATA have;
  INPUT ID $ Team $ Rank $ Point;
  DATALINES;
111 T1 A1 0
112 T1 A2 300
113 T1 A2 600
114 T1 A3 400
115 T1 A3 100
116 T2 A1 0
117 T2 A2 1000
118 T2 A2 1600
119 T2 A3 1400
120 T2 A3 1100
;
run;

proc sql;
select *,case when rank='A1' then
(select sum(point) from have where rank in ('A2' 'A3') and team=a.team)
when rank='A2' then (select sum(point) from have where rank ='A3' and  team=a.team)
when rank='A3' then point
else . end as tpoints
 from have as a ;
quit;    

[Haikuo · Accepted solution]

If you have more than 3 ranks following the same rule within one team and you have an existing ranking heirarchy as is or similar, Give a shot at the following code:

DATA have;
  INPUT ID $ Team $ Rank $ Point;
  DATALINES;
111 T1 A1 0
112 T1 A2 300
113 T1 A2 600
114 T1 A3 400
115 T1 A3 100
116 T2 A1 0
117 T2 A2 1000
118 T2 A2 1600
119 T2 A3 1400
120 T2 A3 1100
;
run;


proc sql;
create table want as
select *, coalesce((select sum(point) from have where team=a.team and rank > a.rank),point) as tpoints
  from have a;

quit;