Moving 2nd, 3rd largest number per group

[hhchenfx]

Hi Everyone,

I have panel data with date, id and values.

For a give day and id, I want to look back 3 days (so including current day, there are 4 days in total) and find the 1st, 2nd, 3rd largest. It is like moving largest(n).

So for day 6 and id = 1, the desired result should be: 9, 4 , 2

So for day 5 and id = 1, the desired result should be: 12, 9 , 4

Can you please help?

Thanks,

HHC
(My work requires multiple lookback windows (say, 20, 40 days) and apply to multiple columns. For that, I guess a macro can be fine and I think I can handle that)

data have;
input day id v1 v2 v3 v4;
datalines;
6 1 2 15 3 1
5 1 4 50 6 9
4 1 1 0 61 0
3 1 9 0 60 5
2 1 12 3 1 2
1 1 21 2 0 2
6 2 2 5 2 3
5 3 12 15 6 1
4 3 0 1 1 8
3 3 0 4 4 8
2 3 12 6 56 8
1 3 25 5 65 8
;run;

[Reeza · Accepted solution]

I'm assuming you have a typo in your data (line 7, ID=2).

If true, something like this should work:

data have;
input day id v1 v2 v3 v4;
datalines;
6 1 2 15 3 1
5 1 4 50 6 9
4 1 1 0 61 0
3 1 9 0 60 5
2 1 12 3 1 2
1 1 21 2 0 2
6 3 2 5 2 3
5 3 12 15 6 1
4 3 0 1 1 8
3 3 0 4 4 8
2 3 12 6 56 8
1 3 25 5 65 8
;run;


proc sort data=have;
by id day;
run;

data want;
array p{0:3} _temporary_;
set have; 
by ID; 

if first.ID then call missing(of p{*});

p{mod(_n_,4)} = v1;

if day > 3 then do;
Rank1 = largest(1,  of p{*});
Rank2 = largest(2,  of p{*});
Rank3 = largest(3,  of p{*});
end;
run;

[hhchenfx]

Thank you, Reeza.
It works perfectly.
HHC