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
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;
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;
Thank you, Tom, for helping.
I really appreciate it.
HHC
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.