topic Re: SAS query in SAS Programming

SAS query

scb — Sun, 18 Jun 2017 15:16:16 GMT

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.

Re: SAS query

Tom — Sun, 18 Jun 2017 17:17:26 GMT

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;

Re: SAS query

art297 — Sun, 18 Jun 2017 16:15:11 GMT

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

Re: SAS query

Ksharp — Mon, 19 Jun 2017 13:04:46 GMT

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;

Re: SAS query

Haikuo — Mon, 19 Jun 2017 18:57:27 GMT

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;