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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.