DATA Step, Macro, Functions and more

Ranking across rows instead of column

Reply
Occasional Contributor
Posts: 5

Ranking across rows instead of column

Hey guys,

What I am trying to do are following: I want to find out if a observation (A) is top 3 across others.

For example,

     A    B    C    D    E    F    G    H   TOP3-A

1  20   30  40   50  60  70   80  90       N

2  80   90  70   80   0    0     0    0        Y

3  70   0     0    80   90  0    0     0        Y

4  60   70   80  90   0    0     0    0         N

I am thinking transpose + rank + transpose + if <4 then Y else N, however it seems too cumbersome and to be honest as a newbie I do not how to code all these steps correctly...

Thanks!

Super Contributor
Posts: 298

Re: Ranking across rows instead of column

Take the 3-rd largest value. Compare it with A and decide the top3_A flag.

data want;

   set have;

   top3_A = 'N';

   array k

  • A -- H;
  •    top = largest(3, of k

  • );
  •    if A >= top then top3_A = 'Y';

    drop top;

    run;

    Super Contributor
    Posts: 308

    Re: Ranking across rows instead of column

    Hello,

    data have;
    input  A    B    C    D    E    F    G    H ;
    datalines;
    1  20   30  40   50  60  70   80  90
    2  80   90  70   80   0    0     0    0
    3  70   0     0    80   90  0    0     0
    4  60   70   80  90   0    0     0    0
    ;


    data want;
    set have;

    TOP3_A="N";
    do i=1 to 3;
    if B=largest(i, B,C,D,E,F,G,H) then do;
      TOP3_A="Y";
      go to finish;
    end;
    end;

    finish: ;

    drop i;
    run;

    Ask a Question
    Discussion stats
    • 2 replies
    • 184 views
    • 0 likes
    • 3 in conversation