BookmarkSubscribeRSS Feed
renjithr
Quartz | Level 8

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

5 REPLIES 5
Astounding
PROC Star

Why are the two BCA ranks the same?

 

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

renjithr
Quartz | Level 8

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

ballardw
Super User

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
Quartz | Level 8

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

Astounding
PROC Star

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4585 views
  • 0 likes
  • 3 in conversation