topic Re: Find nth highest value within each group in SAS Programming

Find nth highest value within each group

Arun_shSAS — Fri, 17 Aug 2018 04:44:13 GMT

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();

Re: Find nth highest value within each group

ChrisNZ — Fri, 17 Aug 2018 05:13:45 GMT

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

Re: Find nth highest value within each group

KachiM — Fri, 17 Aug 2018 06:08:07 GMT

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

Re: Find nth highest value within each group

Kurt_Bremser — Fri, 17 Aug 2018 07:47:29 GMT

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

Re: Find nth highest value within each group

RW9 — Fri, 17 Aug 2018 07:50:26 GMT

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

Re: Find nth highest value within each group

Patrick — Fri, 17 Aug 2018 08:50:07 GMT

@Arun_shSAS

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

Re: Find nth highest value within each group

mkeintz — Sun, 19 Aug 2018 02:55:28 GMT

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.