how to find second highest score in each group by proc sql

[Kurt_Bremser]

Please supply example data against which we can test code. See my footnotes for hints about posting data and code.

If this is some kind of homework, you should be able to solve the problem yourself, if your teacher is worth the money.

[thanikondharish]

no one ask this question. i got answer by using data step block but i want
in proc sql

[RW9]

Again, please present a full question, with text in the body of the post.  Present test data in the form of a datastep, and show what you want as output.  

If you have the answer as datastep, please clarify the need for sql.  It is a far simpler problem in datastep, than in sql.

[PeterClemmensen]

If you already have a working data step solution, why do you want to do it in PROC SQL?

[Ksharp]

SQL is not right tool to do it .

data have;
 set sashelp.class;
 score=weight;
 keep sex score;
run;
proc sql;
create table want as
 select *
  from (
select * from have group by sex having score ne max(score)
)
group by sex
 having score=max(score);
quit;

[hashman]

Ksharp,

I agree it's not a right tool. But your SQL code still gets there - under two conditions. First, unless the specs say "I want all the records with the second highest score", a DISTINCT would be needed in the outer query to weed out possible ties. Second, if a key group has but a single record, your code will filter it out. Again, it may be just what's required, but a different set of specs may say that it should be in the output. In this case, a good SQL head may still be able to adjust for it at the expense of more convoluted code, but then it would only reinforce your point about choosing an appropriate tool.  

Best

Paul D.

[Ksharp]

data have;
 set sashelp.class;
 score=weight;
 keep sex score;
run;
proc sql;
create table want as
 select *,(select count(*) from (select distinct sex,score from have) 
where sex=a.sex and score>=a.score) as rank
  from have as a
   where calculated rank=2;
quit;

[niloya]

@Ksharp  

The SAS code you recommend working perfectly. Thank you so much. I just wanna say that this one resolved my important problem.