nth max value in proc sql

[BrahmanandaRao]

Hi 

How to find 4th max value in proc sql 

[PaigeMiller]

Use PROC SORT and a DATA step, or PROC RANK.

PROC SQL is a particularly poor tool for this task.

[SteveDenham]

PROC UNIVARIATE will default to outputting the top 5 ranking values and the bottom 5 ranking values, so you might consider it as well.

SteveDenham

[BrahmanandaRao]

Hi PaigeMiller
in proc sql this cannot solve the problem
please tell me

[PaigeMiller]

---

@BrahmanandaRao wrote:
Hi PaigeMiller
in proc sql this cannot solve the problem
please tell me

---

I already told you, PROC SQL is a very poor choice for this problem. The solution is simple using PROC SORT or PROC RANK.

[Ksharp]

SQL is not right tool for this.

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

[PaigeMiller]

On of the reasons you would want to use PROC RANK and not SQL is that PROC RANK gives you control over how ties are handled, which could be important for real data (although it doesn't matter with SASHELP.CLASS).

[BrahmanandaRao]

Hi K Sharp

Is it correct method to find nth max value

data _4th;
input id salary;
datalines;
1 2000
2 6000
3 3000
4 9000
5 4500
6 8000
;
run;


proc sql;
create table nmax  as
select id, salary from _4th
order by salary desc
;
select id ,salary from nmax
where monotonic() =4;
quit;

[Ksharp]

No. I would not trust function "monotonic()" .
It would get the unpredicted result according to sas documentation .
Pick right tool -> data step .

[Kurt_Bremser]

Get rid of your SQL-itis. Use the right tool.