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

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

renjithr — Thu, 01 Dec 2016 00:15:01 GMT

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

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

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

Astounding — Thu, 01 Dec 2016 00:05:30 GMT

Why are the two BCA ranks the same?

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

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

ballardw — Thu, 01 Dec 2016 00:10:21 GMT

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?

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

renjithr — Thu, 01 Dec 2016 00:12:14 GMT

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

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

renjithr — Thu, 01 Dec 2016 00:13:03 GMT

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

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

Astounding — Thu, 01 Dec 2016 01:01:29 GMT

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.