Home > Blockchain >  Rank the equal values of a dataset
Rank the equal values of a dataset

Time:03-29

I'm new to programming in sas and I had a question regarding this topic.

Tengo una tabla de entrada así

  CODE DESCRIPTION   COUNT
  NULL    KIT-KAT      3           
  NULL    KIT-KAT      3           
  NULL    KIT-KAT      3           
  NULL    FERRERO      2           
  NULL    FERRERO      1           

and I would like to have an output table like this:

  CODE DESCRIPTION   COUNT  COLUMN_I_WANT
  NULL    KIT-KAT      3           1
  NULL    KIT-KAT      3           2
  NULL    KIT-KAT      3           3
  NULL    FERRERO      2           1
  NULL    FERRERO      1           2

The problem is that I have tried to group with a rank, I have used monotonic and I have done several things but none of them work for me, I want it to count the equal values for a field but not to repeat the value, but to put 1,2 ,3 in the event that there are 3 repeated values, as it appears in the table that I put in the example.

Here I pass code that I have been testing and it does not work for me

proc sql;
create table test2 AS
select *, count(t2.descripcion_tpv) AS progressive
from rank_2 as t1
LEFT JOIN RANK_2 AS T2 ON (T1.DESCRIPCION_TPV = T2.DESCRIPCION_TPV)
GROUP BY t1.descripcion_tpv
ORDER BY t1.descripcion_tpv;
quit;

CodePudding user response:

Use a data step with by group processing instead.

proc sort data=have;
    by description;
run;

data want;
    set have;
    by description;

    if(first.description) then rank = 0;

    rank 1;
run;

Output:

CODE    DESCRIPTION COUNT   rank
NULL    FERRERO     2       1
NULL    FERRERO     1       2
NULL    KIT-KAT     3       1
NULL    KIT-KAT     3       2
NULL    KIT-KAT     3       3

Note that the sum statement rank 1 is equivalent to:

retain rank 0;
rank = rank 1;
  • Related