I have 100 rows of data and I have an account#, a segment, and a score for each row. I want to create another field(column) for each row that is the highest score for that segment. I figured I would sort everything by segment and then descending score first. But not sure what the next step would be maybe a data step if first.segment and first.score then newvariable = score. ??
Acct# | Segment | Score | New Field |
123 | A | 2 | 14 |
124 | A | 9 | 14 |
125 | A | 14 | 14 |
126 | A | 10 | 14 |
127 | A | 12 | 14 |
128 | A | 3 | 14 |
129 | A | 7 | 14 |
130 | A | 5 | 14 |
230 | A | 8 | 71 |
231 | B | 15 | 71 |
232 | B | 7 | 71 |
233 | B | 22 | 71 |
234 | B | 15 | 71 |
235 | B | 71 | 71 |
236 | B | 45 | 71 |
237 | B | 62 | 71 |
238 | B | 29 | 71 |
239 | B | 30 | 71 |
data have;
input Acct Segment $ Score;
datalines;
123 A 2
124 A 9
125 A 14
126 A 10
127 A 12
128 A 3
129 A 7
130 A 5
230 A 8
231 B 15
232 B 7
233 B 22
234 B 15
235 B 71
236 B 45
237 B 62
238 B 29
239 B 30
;
proc sql;
create table want as
select *,
max(Score) as NewField
from have
group by Segment;
quit;
I don't know if this is the best way to accomplish this task but I was able to do the following.
proc means data = table1 max nonobs noprint;
class segment;
ways 1;
var CEV;
output out= table2 (drop= _freq_ _type_) max=TopScore;
RUN;
proc sql;
create table2 AS
select a.*, b.TopScore
from table1 a left join table2 b ON a.segment=b.segment
QUIT;
data have;
input Acct Segment $ Score;
datalines;
123 A 2
124 A 9
125 A 14
126 A 10
127 A 12
128 A 3
129 A 7
130 A 5
230 A 8
231 B 15
232 B 7
233 B 22
234 B 15
235 B 71
236 B 45
237 B 62
238 B 29
239 B 30
;
proc sql;
create table want as
select *,
max(Score) as NewField
from have
group by Segment;
quit;
data have;
input Acct $ Segment $ Score;
cards;
123 A 2
124 A 9
125 A 14
126 A 10
127 A 12
128 A 3
129 A 7
130 A 5
230 A 8
231 B 15
232 B 7
233 B 22
234 B 15
235 B 71
236 B 45
237 B 62
238 B 29
239 B 30
;
run;
Proc sql;
create table want as select acct, segment, score, (select max(score) from have a
where a.segment=b.segment group by segment) as new_field from have b;
quit;
proc sort data=have;
by segment score;
run;
data want;
do until(last.segment);
set have;
by segment score;
new_field=score;
end;
do until(last.segment);
set have;
by segment score;
output;
end;
run;
Or
data want;
do until (last.segment);
set have;
by Segment;
NewField=max(NewField, Score);
end;
do until (last.segment);
set have;
by Segment;
output;
end;
run;
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.