DATA Step, Macro, Functions and more

SAS query

Accepted Solution Solved
Reply
Contributor scb
Contributor
Posts: 50
Accepted Solution

SAS query

[ Edited ]

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.


Accepted Solutions
Solution
‎06-28-2017 09:35 AM
Respected Advisor
Posts: 3,124

Re: SAS query

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; 

View solution in original post


All Replies
Super User
Super User
Posts: 6,502

Re: SAS query

[ Edited ]

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;
PROC Star
Posts: 7,366

Re: SAS query

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

 

Super User
Posts: 9,691

Re: SAS query

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;    
Solution
‎06-28-2017 09:35 AM
Respected Advisor
Posts: 3,124

Re: SAS query

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; 
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 169 views
  • 0 likes
  • 5 in conversation