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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.