Find Nth largest within each group for moving window

[hhchenfx]

Hi Everyone,

My data has date, ID and value.

For each date and ID, say date=7 and ID = 1, I look back 5 days (from date = 6 to date = 2) and find the 1st highest, 2nd highest and 3rd highest values.

So the wanted data should have 3 more new columns for these highest.

Can you please help me?

Thank you,

HHC

data have;
infile cards;
input date ID value;
cards;
1 1 1
2 1 7
3 1 3
4 1 101
5 1 102
6 1 103
7 1 104
8 1 105
8 2 1
9 2 124
10 2 11
11 2 9999
12 2 9
;
run;

a 

[Tom · Accepted solution]

If you have SAS/ETS installed use the procedures to do these things.

If not use a "circular" array.

data have;
  input date ID value;
cards;
1 1 1
2 1 7
3 1 3
4 1 101
5 1 102
6 1 103
7 1 104
8 1 105
8 2 1
9 2 124
10 2 11
11 2 9999
12 2 9
;

data want;
  set have;
  by id date ;
  array history [0:4] _temporary_;
  first=largest(1,of history[*]);
  second=largest(2,of history[*]);
  third=largest(3,of history[*]);
  if first.id then call missing(of index first second third history[*]);
  index+1;
  history[mod(index,5)]=value;
run;

proc print;
run;

Result:

Obs    date    ID    value    first    second    third    index

  1      1      1        1        .        .        .       1
  2      2      1        7        1        .        .       2
  3      3      1        3        7        1        .       3
  4      4      1      101        7        3        1       4
  5      5      1      102      101        7        3       5
  6      6      1      103      102      101        7       6
  7      7      1      104      103      102      101       7
  8      8      1      105      104      103      102       8
  9      8      2        1        .        .        .       1
 10      9      2      124        1        .        .       2
 11     10      2       11      124        1        .       3
 12     11      2     9999      124       11        1       4
 13     12      2        9     9999      124       11       5

If you want to include the current value in the search then re-order the statements.

data want;
  set have;
  by id date ;
  array history [0:4] _temporary_;
  if first.id then call missing(of index first second third history[*]);
  index+1;
  history[mod(index,5)]=value;
  first=largest(1,of history[*]);
  second=largest(2,of history[*]);
  third=largest(3,of history[*]);
run;

[hhchenfx]

Thank you, Tom, for helping.

I really appreciate it.

HHC

[PeterClemmensen]

You can let Proc Summary find the top 3 values for you.

data have;
infile cards;
input date ID value;
cards;
1  1 1    
2  1 7    
3  1 3    
4  1 101  
5  1 102  
6  1 103  
7  1 104  
8  1 105  
8  2 1    
9  2 124  
10 2 11   
11 2 9999 
12 2 9    
;
run;

proc summary data = have nway noprint;
   class ID;
   var value;
   output out = want(drop = _:) idgroup (max(value) out[3] (value) =) / autolabel autoname;
run;