Find nth highest value within each group

[Arun_shSAS]

data a;
input id sal;
cards;
1 10
1 20 
1 40 
1 35 
1 5
2 3 
2 6
2 4 
2 78
;
run;

* I want within each distinct group of id I want 3rd largest sal value using monotonic();

[ChrisNZ]

Why monotonic()?

SQL is just not good at considering rows in order, unless ORDER BY is used.

This does what you want:

proc sql;
  select a.ID, a.SAL, count(*) as ORDER 
  from HAVE a
      ,HAVE b
  where a.ID=b.ID and a.SAL<=b.SAL
  group by 1,2
  having ORDER=3;
quit;

ID	SAL	ORDER
1	20	3
2	4	3

[KachiM]

If you want a Data Step Solution ...

Data Set A is assumed to be sorted by ID. Assumed that within ID there will not be  more than 100 SAL values. Used Array to store SALs for each ID. Use LARGEST function to get your Kth ORDERED Value from the Array. Clean the Array and process the next ID as above.

data a;
input id sal;
cards;
1 10
1 20 
1 40 
1 35 
1 5
2 3 
2 6
2 4 
2 78
;
run;

data want;
   array k[100] _temporary_;
   do i = 1 by 1 until(last.id);
      set a;
      by id notsorted;
      k[i] = sal;
   end;
   third_Largest = Largest(3, of k[*]);
   put third_largest =;
   call missing(of k[*]);
run;

Cheers.

DATASP

[Kurt_Bremser]

---

@Arun_shSAS wrote:

data a;
input id sal;
cards;
1 10
1 20 
1 40 
1 35 
1 5
2 3 
2 6
2 4 
2 78
;
run;

* I want within each distinct group of id I want 3rd largest sal value using monotonic();

---

Basic data step solution:

proc sort data=a;
by id descending sal;
run;

data want;
set a;
by id;
if first.id
then count = 1;
else count + 1;
if count = 3;
drop count;
run;

[RW9]

%let nth=3;

data a;
  input id sal;
cards;
1 10
1 20 
1 40 
1 35 
1 5
2 3 
2 6
2 4 
2 78
;
run;

proc sort data=a;
  by id descending sal;
run;

data want;
  set a;
  retain cnt;
  by id;
  cnt=ifn(first.id,1,cnt+1);
  if cnt=&nth.;
run;

Combine that with any other processing.

[Patrick]

@Arun_shSAS

monotonic() is an undocumented and unsupported function which you shouldn't use at all.

[mkeintz]

You might use monotonic() to examine some of the behavior of sql, but I wouldn't recommend it for work you rely on.

Neither sql nor the data step is the most efficacious solution.  I'd recommend proc rank (assuming data are sorted by id):

proc rank data=have out=want (where=(salrank=3)) descending;
  by id;
  var sal;
  ranks salrank;
run;

If the data were already sorted by descending salary within id, then the data step would be easiest.  But if the within-id records have any other order, proc rank is a very direct solution.