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

[renjithr]

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

[Astounding]

Why are the two BCA ranks the same?

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

[renjithr]

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

[ballardw]

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?

[renjithr]

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

[Astounding]

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.