topic Re: nth max value in proc sql in Statistical Procedures

nth max value in proc sql

BrahmanandaRao — Tue, 29 Sep 2020 10:57:53 GMT

How to find 4th max value in proc sql

Re: nth max value in proc sql

PaigeMiller — Tue, 29 Sep 2020 11:18:44 GMT

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

PROC SQL is a particularly poor tool for this task.

Re: nth max value in proc sql

SteveDenham — Tue, 29 Sep 2020 11:20:31 GMT

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

Re: nth max value in proc sql

BrahmanandaRao — Tue, 29 Sep 2020 11:24:34 GMT

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

Re: nth max value in proc sql

PaigeMiller — Tue, 29 Sep 2020 11:27:21 GMT

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

Re: nth max value in proc sql

Ksharp — Tue, 29 Sep 2020 11:27:36 GMT

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;

Re: nth max value in proc sql

PaigeMiller — Tue, 29 Sep 2020 11:58:57 GMT

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).

Re: nth max value in proc sql

Kurt_Bremser — Tue, 29 Sep 2020 12:06:11 GMT

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

Re: nth max value in proc sql

BrahmanandaRao — Mon, 09 Aug 2021 05:12:43 GMT

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;

Re: nth max value in proc sql

Ksharp — Mon, 09 Aug 2021 12:40:07 GMT

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