Find second largest no in Proc SQL

[Pooja98]

Name Mark
Ram 98
Seetha 87
Rahul 96
Malar 83
Divya 90
How can I find the second largest number by using proc sql?

[rudfaden · Accepted solution]

like this

data have;
input name $ mark;
cards;
Ram 98
Seetha 87
Rahul 96
Malar 83
Divya 90
;
run;

proc sql;
	SELECT distinct max(mark)
		FROM have
			where mark < 
				(SELECT MAX(mark) FROM have);
quit;

[Kurt_Bremser]

How do you want to deal with ties?

[shweta_d_singh]

try using this:

proc sql;
create table want as
select *
from have
having max(mark) ne mark;

create table want1 as
select *
from want
having max(mark) eq mark;
quit;

[Amir]

Hi @Pooja98 ,

Thanks for providing some input data; if you could also share what SAS code you have tried with your results and / or any log messages then that would benefit yourself and everyone else.

Please clarify the reason for the requirement to use proc sql, as opposed to any other proc or data step, as this sounds like quite a restrictive work practice unless this is classwork / homework.

Off the top of my head proc sql could be used to select the maximum as long as it is not equal to the maximum of the data set. For example:

proc sql noprint;
   create table
      second
   as
   select
      max(weight) as weight
   from
      sashelp.class
   where
      weight not in (select max(weight) from sashelp.class)
   ;
quit;

Kind regards,

Amir.

[novinosrin]

Good morning @Pooja98  Your question could be addressed as an item to compute ranks highest to lowest  and keep a set with all ranked scores using which you could subset a rank of your choice. 

Assuming you do not have ties in your score, you could keep it simple.

data have;
input name $ mark;
cards;
Ram 98
Seetha 87
Rahul 96
Malar 83
Divya 90
;
run;

proc sql;
 create table want as
 select a.*	,count(b.mark) as rank
 from have a left join have b
 on b.mark>=a.mark
 group by a.name,a.mark
 order by rank;
quit;

The above can be extended to subsetting a rank of your choice by defining a macro variable that has the subset defintion. For example

%let rank=2	; /*Define your rank here*/
proc sql;
 create table want as
 select a.*	,count(b.mark) as rank
 from have a left join have b
 on b.mark>=a.mark
 group by a.name,a.mark
 having rank=&rank;
quit;

[PaigeMiller]

In my opinion, the answer is PROC RANK, not PROC SQL.

PROC RANK gives you options to deal with ties, while PROC SQL does not. I urge people to get in the habit of using PROC RANK to rank, instead of PROC SQL.

In the example data, there are no ties, but in real life there may be. If, for example, the real-world application is to find who is the second highest ranking student in a class to give them an award, and you don't account for ties by using PROC SQL, then ... OOOPS! Maybe worse than OOOPS, maybe LAWSUIT!

[novinosrin]

Sir @PaigeMiller  I can't agree more. However, I must also say veterans like you, @ballardw  etc. are less likely to be faced with such irrelevant questions in an interview.  However, for the younger and innocent lot, I believe this could be some silly interview question to bully the candidate or perhaps even a casual way to bully someone who is weaker. 

What you may miss is the "unprofessional" attitudes, aspects behind the scenes. There are such personalities in this globe who think that they know everything and in my opinion are actually good for nothing. So perhaps the OP is/was also faced with such a situation. 

Not so long ago, I was tested for "syntax" by a so called educated professional. Of course, I don't remember or keep up with syntax. And he was like, "you do not know SAS at all". So yeah, hmm such could be the case. 

To end with some humor, if you do not learn to do spaghetti code, you won't do well in my bank. lol 🙂

[SASKiwi]

@novinosrin  - IMHO avoiding job opportunities where you are expected to be a SAS syntax dictionary is a good idea. Its probably a symptom of a more serious disease...

[Ksharp]

data have;
input name $ mark;
cards;
Ram 98
Seetha 87
Rahul 96
Malar 83
Divya 90
;
run;

proc sql;
 select a.*	,
 (select count(distinct mark) from have where mark>a.mark) as rank
 from have as a
 where calculated rank=1; 
quit;