BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Rhodochrosite | Level 12

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

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
Rhodochrosite | Level 12

Thank you, Tom, for helping.

I really appreciate it.

HHC

PeterClemmensen
Tourmaline | Level 20

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1295 views
  • 4 likes
  • 3 in conversation