CODEWITHSUNDEEP
sas
Robert Penridge
Robert Penridge

Reputation: 8513

Avoinding duplicate ranks with PROC RANK

I would like to use PROC RANK to rank multiple variables. For simplicity the code below only demonstrates using a single variable.

In the case of a tie, I would like proc rank to just iterate across the next rank values. The example code I give below gives the duplicate values (333) ranks of 3.5 and 3.5. I would like it to just assign 3 and 4 instead.

Is there a way to do this using PROC RANK? I can fix it in a data step but if I can do it within the proc then that will keep things neater.

data have;
  input x;
  datalines;
100
200
333
333
;
run;

proc rank data=have out=dont_want;
  var x;
  ranks x_rank;
run;

Upvotes: 0

Views: 923

Answers (1)

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

You're better off with a data step if you need to consistently increase the rank.

proc sort data=have;
    by x;
run;

data want;
    set have;
    rank+1;
run;

If you have many variables to rank, you can use this macro below:

%macro sequential_rank(lib=, dsn=, out=);

    data want;
        set have;
    run;

    proc sql noprint;
        select name
        into :vars separated by '|'
        from dictionary.columns
        where     memname = %upcase("&dsn.")
              AND libname = %upcase("&lib.")
              AND type    = 'num'
        ;
    quit;

    %do i = 1 %to %sysfunc(countw(&vars., |));
        %let var = %scan(&vars., &i., |);

        proc sort data=&out.;
            by &var.;
        run;

        data &out.;
            set &out.;
            by &var.;

            rank_&var.+1;
        run;
    %end;
%mend;
%sequential_rank(lib=work, dsn=have, out=want);

Upvotes: 1

Related Questions