DATA Step, Macro, Functions and more

How to assign rank to a character variable in a group?

Reply
Frequent Contributor
Posts: 122

How to assign rank to a character variable in a group?

[ Edited ]

Hello,

 

I have a data set that contains vendor, vendor location, products , product category. I want to find out the top 5 products per vendor location, and vendor.

eg: Vendor Vendor_loc products Prod_cat

         ABC     CA             TV      ENT

         ABC    CA             TV      ENT

          BCA     CA             IPAD      ENT

          BCA     CA             TV     ENT

         ABC    CA             IPAD      ENT

         ABC      CA            TV      ENT

 

Want:

     Vendor Vendor_loc   products  rank

      ABC       CA              TV            1

      ABC        CA              IPAD        2

      BCA       CA              IPAD         1

       BCA      CA               TV            1

 

Could you please let me know how I can rank products(character variable) in a group?

 

Thanks

Super User
Posts: 5,081

Re: How to assign rank to a character variable in a group?

Why are the two BCA ranks the same?

 

If you have a tie, what ranks do you want to assign?

Frequent Contributor
Posts: 122

Re: How to assign rank to a character variable in a group?

If they there is tie then same rank or different rank is fine.

Super User
Posts: 10,497

Re: How to assign rank to a character variable in a group?

What is your definition of "top"? Most frequent value in the data, related to a value not shown (revenue, market share, show ratings, color of logo), or something less obvious?

Frequent Contributor
Posts: 122

Re: How to assign rank to a character variable in a group?

Vendor with most products in a location. For eg: ABC had TV as their top product(with rank 1) in CA .

Super User
Posts: 5,081

Re: How to assign rank to a character variable in a group?

Here's one approach:

 

proc freq data=have noprint;

tables vendor * vendor_loc * product / noprint out=counts (drop=percent);

run;

 

proc sort data=counts;

by vendor vendor_loc descending count;

run;

 

data want;

set counts;

by vendor vendor_loc;

if first.vendor_loc then rank=1;

else rank + 1;

run;

 

Conceivably you could drop COUNT at the end but I left it in because you might find it useful.

Ask a Question
Discussion stats
  • 5 replies
  • 265 views
  • 0 likes
  • 3 in conversation