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.
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;
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;
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
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;
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;
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.
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.