SAS: How to assign the first value with a higher rank in a group to the entire group

Question

In SAS, how can I assign the first Rating value with a higher rank in a group to the entire group. See below data set

ID  Rating  Price   Rt_Rank
AN  A       105     0
AN  B3      200     1
IG  A2      705     0
IG  A       700     1
IG  HY      102     1
IG  NR      1005    1
RS  HY      20      1
AK  NR      803     0
DC  A       0       0
DC  NR      12000   0

if for a group rt_rank is 1 then I want to assign the first value rating to the entire group.

Desired dataset:

ID  Rating  Price   Rt_Rank Rating_grp
AN  B       105     0       B3
AN  B3      200     1       B3
IG  A2      705     0       A
IG  A       700     1       A
IG  HY      102     1       A
IG  NR      1005    1       A
RS  HY      20      1       HY
AK  NR      803     0       NR
DC  A       0       0       NR
DC  NR      12000   0       NR

I achieved this rating_grp by creating a intermediate dataset and creating a temporary variable with this first rating value and then merge it with the original dataset. But I want to know if there is a alternate and easy way to do this.

Thanks in advance!

How do you pick 'A' over 'HY' and 'NR' Ratings for the 'IG' ID group? Why did 'NR' get chosen for the 'AK' and 'DC' groups? — mjsqu, Nov 18 '14 at 15:19

user667489 · Answer 1 · 2014-11-18T20:35:16.250

It isn't clear what rule you want to use if all the Rating values have rank 0. However, the following is pretty close to what you want. It uses a double DOW-loop to calculate a rolling max within each ID by-group and then attach it to every record in that by-group. This means you have to read the input dataset twice, but you avoid producing an intermediate dataset.

The example data you've posted isn't sorted by ID, so I'm assuming that if the same ID comes up twice in non-contiguous rows, you want to calculate separate rating_grp values for each instance.

data have;
    length ID RATING $2;
    input ID $ Rating $  Price  Rt_Rank;
    cards;
AN  A       105     0
AN  B3      200     1
IG  A2      705     0
IG  A       700     1
IG  HY      102     1
IG  NR      1005    1
RS  HY      20      1
AK  NR      803     0
DC  A       0       0
DC  NR      12000   0
;
run;

data want;
    if 0 then set have; /*Make sure columns are in correct order in output dataset*/
    length rating_grp $2;
    max_rank = -1; /*Before we start each by group, reset the rolling max*/
    call missing(rating_grp);
    do until(last.ID); /*Work through each by group keeping a rolling max*/
        set have;
        by ID notsorted;
        if rt_rank > max_rank then do; /*Update rating_grp each time a new max is reached*/
            max_rank = rt_rank;
            rating_grp = rating;
        end;
    end;
    do until(last.ID); /*Work through the by group a second time, this time outputting all records*/
        set have;
        by ID notsorted;
        output;
    end;
    drop max_rank;
run;

SAS: How to assign the first value with a higher rank in a group to the entire group

1 Answers1