topic proc sql in SAS Programming

proc sql

vandhan — Thu, 28 Jan 2016 12:19:54 GMT

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

Re: proc sql

LinusH — Thu, 28 Jan 2016 12:45:16 GMT

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?

Re: proc sql

RW9 — Thu, 28 Jan 2016 13:00:35 GMT

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.

Re: proc sql

BMiller — Thu, 28 Jan 2016 14:49:52 GMT

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

Re: proc sql

vandhan — Thu, 28 Jan 2016 14:54:39 GMT

One or many.

Re: proc sql

BMiller — Thu, 28 Jan 2016 15:28:05 GMT

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;