proc sql

[vandhan]

data have;
input id Salary;
cards;
101 1000
102 2000
103 1000
104 3000
105 3000
106 4000

107 2000
;

wanted to find id and salary which is 3rd max value using sql??

in this case result should be 102, 107 with 2000 salary which is 3rd maximum

[LinusH]

Well, you can't exactly. You could use the undocumented/unsupported function monotonic() (gives you record no) which could be applied after a sort.

But why are you fixed with SQL?

[RW9]

Post an example of your exact data, as per the duplicate post, there is more than one variable.  I would suggest a datastep, where you have an array of 3, each one containing the max e.g:

data want;
  set have;
  array results{6,3} 8.;
  retain results1-results3;
  array items{6} your_variable1-your_variable6;
  do i=1 to 6;
    if items{i} > results{i,1} then results{i,1}=items{i};
    else if items{i} > results{i,2} then results{i,2}=items{i};
    else if items{i} > results{i,3} then results{i,3}=items{i};
  end;
run;
  

You will then have a 6 * 3 array of variables which contain varnumber (1-6) of the top 3 results.  You can use this how you wish, maybe by a second datastep (or set command) which outputs when the value=the corresponding array element.  

[BMiller]

1 sql step or can this be processed in a couple of steps?

[vandhan]

One or many. 

[BMiller · Accepted solution]

I can do it in 2 using a macro assignment:

proc sql;
select distinct salary
into :top1 - :top3
from have
order by salary desc
;
quit;

proc sql;
select * from have
where salary = &top3.
;
quit;