Hi,
I have a table table_1
st_name | class | subject | score |
Allen | A | math | 99 |
Allen | A | science | 90 |
Bella | A | math | 95 |
Amelia | B | science | 88 |
Benson | B | math | 80 |
proc sql;
create table max_subject as select A.*,
case when A.score eq max_score then A.subject end as max_subj
from
(select distinct * , max(score) as max_score from table_1 group by st_name) as A;
order by st_name, class;
quit
I want result as
st_name | class | subject | score | best_sub |
Allen | A | math | 99 | math |
Allen | A | science | 90 | math |
Bella | A | math | 95 | math |
Amelia | B | science | 88 | music |
Amelia | B | music | 99 | music |
Benson | B | math | 80 | math |
However, I run my script and I get
st_name | class | subject | score | best_sub |
Allen | A | math | 99 | math |
Allen | A | science | 90 | |
Bella | A | math | 95 | math |
Amelia | B | science | 88 | |
Amelia | B | music | 99 | music |
Benson | B | math | 80 | math |
How can I correct my code?
Please advise, thank you!
@sarahzhou: Try this for the case where a st_name (ALLEN in this example) gets a 99 in both classes:
data have;
input st_name $ class $ subject $ score;
datalines;
Allen A math 99
Allen A science 99
Bella A math 95
Amelia B science 88
Amelia B music 99
Benson B math 80
;
proc sql;
create table tmp_want1 as
select a.st_name, b.best_sub,b.best_score
from (select distinct st_name from have)a
left join
(select st_name, subject as best_sub, max(score) as best_score
from have a
group by st_name
having max(score) = score
) b
on a.st_name = b.st_name
;
quit;
data tmp_want2;
length best_sub_cat $64.;
do until (last.st_name);
set tmp_want1;
by st_name;
best_sub_cat=catx(', ',best_sub_cat,best_sub);
end;
drop best_sub;
run;
proc sql;
create table want_final as
select a.*, b.best_score, b.best_sub_cat
from have a
left join tmp_want2 b
on a.st_name = b.st_name
;
quit;
Sorry, there is a typo in table_1
it should be like this
table_1
st_name | class | subject | score |
Allen | A | math | 99 |
Allen | A | science | 90 |
Bella | A | math | 95 |
Amelia | B | science | 88 |
Amelia | B | music | 99 |
Benson | B | math | 80 |
expect result is:
st_name | class | subject | score | best_sub |
Allen | A | math | 99 | math |
Allen | A | science | 90 | math |
Bella | A | math | 95 | math |
Amelia | B | science | 88 | music |
Amelia | B | music | 99 | music |
Benson | B | math | 80 | math |
Thanks!
Please help T.T
Try this
data have;
input st_name $ class $ subject $ score;
datalines;
Allen A math 99
Allen A science 90
Bella A math 95
Amelia B science 88
Amelia B music 99
Benson B math 80
;
proc sql;
create table want as
select b.*
, a.best_sub
from (select st_name, subject as best_sub
from have a
group by st_name
having max(score) = score
) a
, have b
where a.st_name = b.st_name
;
quit;
Result
st_name class subject score best_sub Allen A math 99 math Allen A science 90 math Bella A math 95 math Amelia B science 88 music Amelia B music 99 music Benson B math 80 math
What do you want to see happen if Allen gets a 99 in both classes?
Hi @PaigeMiller , if Allen gets a 99 in both classes, then both subjects will be returned in column best_sub if those subject has the same max. score. Thanks!
@sarahzhou: Try this for the case where a st_name (ALLEN in this example) gets a 99 in both classes:
data have;
input st_name $ class $ subject $ score;
datalines;
Allen A math 99
Allen A science 99
Bella A math 95
Amelia B science 88
Amelia B music 99
Benson B math 80
;
proc sql;
create table tmp_want1 as
select a.st_name, b.best_sub,b.best_score
from (select distinct st_name from have)a
left join
(select st_name, subject as best_sub, max(score) as best_score
from have a
group by st_name
having max(score) = score
) b
on a.st_name = b.st_name
;
quit;
data tmp_want2;
length best_sub_cat $64.;
do until (last.st_name);
set tmp_want1;
by st_name;
best_sub_cat=catx(', ',best_sub_cat,best_sub);
end;
drop best_sub;
run;
proc sql;
create table want_final as
select a.*, b.best_score, b.best_sub_cat
from have a
left join tmp_want2 b
on a.st_name = b.st_name
;
quit;
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.