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
Why are the two BCA ranks the same?
If you have a tie, what ranks do you want to assign?
If they there is tie then same rank or different rank is fine.
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?
Vendor with most products in a location. For eg: ABC had TV as their top product(with rank 1) in CA .
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.