Return the max value for it's name in the proc sql

[sarahzhou]

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]

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

[PeterClemmensen]

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

[PaigeMiller]

What do you want to see happen if Allen gets a 99 in both classes?

[sarahzhou]

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!

[SubbuPaz · Accepted solution]

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

[sarahzhou]

Wow, cool! Thank you!