proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

proc sql


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


Accepted Solutions
Solution
‎01-28-2016 10:38 AM
Occasional Contributor
Posts: 12

Re: proc sql

 

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;

View solution in original post


All Replies
Super User
Posts: 5,386

Re: proc sql

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?

Data never sleeps
Super User
Super User
Posts: 7,720

Re: proc sql

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.  

Occasional Contributor
Posts: 12

Re: proc sql

1 sql step or can this be processed in a couple of steps?
Contributor
Posts: 36

Re: proc sql

One or many. 
Solution
‎01-28-2016 10:38 AM
Occasional Contributor
Posts: 12

Re: proc sql

 

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 453 views
  • 0 likes
  • 4 in conversation