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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.