DATA have;
INPUT ID $ TeamName $ Team $ Rank $ Point;
DATALINES;
111 Super Head A1 0
112 Super T2 A2 0
114 Super T2 A3 800
115 Super T2 A3 100
116 Super T2 A3 200
112 Super T3 A2 0
114 Super T3 A3 400
115 Super T3 A3 200
116 Super T3 A3 200
117 Super T3 A3 800
111 Tiger Head A1 0
112 Tiger T2 A2 0
114 Tiger T2 A3 800
115 Tiger T2 A3 100
116 Tiger T2 A3 300
112 Tiger T3 A2 0
114 Tiger T3 A3 400
115 Tiger T3 A3 200
116 Tiger T3 A3 200
117 Tiger T3 A3 1000
;
run;
Desired Output:
ID TeamName Team Rank Ponts TPoints Person_inherited
111 Super Head Head 0 2700 7
112 Super T2 A2 0 1100 3
114 Super T2 A3 800 800 0
115 Super T2 A3 100 100 0
116 Super T2 A3 200 200 0
112 Super T3 A2 0 1600 4
114 Super T3 A3 400 400 0
115 Super T3 A3 200 200 0
116 Super T3 A3 200 200 0
117 Super T3 A3 800 800 0
111 Tiger Head Head 0 2800 6
112 Tiger T2 A2 0 1200 3
114 Tiger T2 A3 800 800 0
115 Tiger T2 A3 100 100 0
116 Tiger T2 A3 300 300 0
112 Tiger T3 A2 0 1600 3
114 Tiger T3 A3 400 400 0
116 Tiger T3 A3 200 200 0
117 Tiger T3 A3 1000 1000 0
May I know how to get the TPoints and person inherited? Rank A2 Tpoints will be from all A3 within the team. A2 T Points will be from A3 within the team; A3 Tpoints will be his own pts.
Head will get the accumulated points from Rank A2 within his team.
Person inherited for A3 will be 0, A2 will be the total A3 ID, while Head will get the accumulated person inherited from Rank A2 within his team.
Anyone can help? thanks.
I think the reason that no one responded is because you didn't provide enough information.
Regardless, I was bored this afternoon, so decided to try and solve WHAT I THINK is the problem you are trying to solve.
Take a look at the following and let me know if it does what you want. The result doesn't meet your specs because, for whatever reason, you droped "115 Tiger T3 A3 200 " from your desired output:
DATA have; INPUT ID $ TeamName $ Team $ Rank $ Point; DATALINES; 111 Super Head A1 0 112 Super T2 A2 0 114 Super T2 A3 800 115 Super T2 A3 100 116 Super T2 A3 200 112 Super T3 A2 0 114 Super T3 A3 400 115 Super T3 A3 200 116 Super T3 A3 200 117 Super T3 A3 800 111 Tiger Head A1 0 112 Tiger T2 A2 0 114 Tiger T2 A3 800 115 Tiger T2 A3 100 116 Tiger T2 A3 300 112 Tiger T3 A2 0 114 Tiger T3 A3 400 115 Tiger T3 A3 200 116 Tiger T3 A3 200 117 Tiger T3 A3 1000 ; run; data need; do until (last.TeamName); set have; by TeamName; if first.TeamName then do; tpoints=0; Person_inherited=0; end; else do; tpoints+point; if point gt 0 then Person_inherited+1; end; end; do until (last.TeamName); set have; by TeamName Team; if first.TeamName then output; else do; tpoints=0; Person_inherited=0; output; end; end; run; data want (drop=_:); do until (last.Team); set need (rename=(tpoints=_tpoints Person_inherited=_Person_inherited)); by TeamName Team; if Team eq 'Head' then do; tpoints=_tpoints; Person_inherited=_Person_inherited; end; else do; if first.Team then do; tpoints=0; Person_inherited=0; end; else do; tpoints+point; if point gt 0 then Person_inherited+1; end; end; end; do until (last.Team); set need (rename=(tpoints=_tpoints Person_inherited=_Person_inherited)); by TeamName Team; if first.Team then output; else do; tpoints=0; Person_inherited=0; output; end; end; run;
Art, CEO, AnalystFinder.com
I think the reason that no one responded is because you didn't provide enough information.
Regardless, I was bored this afternoon, so decided to try and solve WHAT I THINK is the problem you are trying to solve.
Take a look at the following and let me know if it does what you want. The result doesn't meet your specs because, for whatever reason, you droped "115 Tiger T3 A3 200 " from your desired output:
DATA have; INPUT ID $ TeamName $ Team $ Rank $ Point; DATALINES; 111 Super Head A1 0 112 Super T2 A2 0 114 Super T2 A3 800 115 Super T2 A3 100 116 Super T2 A3 200 112 Super T3 A2 0 114 Super T3 A3 400 115 Super T3 A3 200 116 Super T3 A3 200 117 Super T3 A3 800 111 Tiger Head A1 0 112 Tiger T2 A2 0 114 Tiger T2 A3 800 115 Tiger T2 A3 100 116 Tiger T2 A3 300 112 Tiger T3 A2 0 114 Tiger T3 A3 400 115 Tiger T3 A3 200 116 Tiger T3 A3 200 117 Tiger T3 A3 1000 ; run; data need; do until (last.TeamName); set have; by TeamName; if first.TeamName then do; tpoints=0; Person_inherited=0; end; else do; tpoints+point; if point gt 0 then Person_inherited+1; end; end; do until (last.TeamName); set have; by TeamName Team; if first.TeamName then output; else do; tpoints=0; Person_inherited=0; output; end; end; run; data want (drop=_:); do until (last.Team); set need (rename=(tpoints=_tpoints Person_inherited=_Person_inherited)); by TeamName Team; if Team eq 'Head' then do; tpoints=_tpoints; Person_inherited=_Person_inherited; end; else do; if first.Team then do; tpoints=0; Person_inherited=0; end; else do; tpoints+point; if point gt 0 then Person_inherited+1; end; end; end; do until (last.Team); set need (rename=(tpoints=_tpoints Person_inherited=_Person_inherited)); by TeamName Team; if first.Team then output; else do; tpoints=0; Person_inherited=0; output; end; end; run;
Art, CEO, AnalystFinder.com
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.