BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

4 REPLIES 4
Tom
Super User Tom
Super User

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
Opal | Level 21

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
Super User
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
Onyx | Level 15

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; 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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