Try
proc sql;
create table want as
select country, productid, max(quantity) as maxqty
from new
group by country
having quantity = max(quantity);
quit;
Please post the code you already tried, and some example data for testing.
Post data in a data step with datalines.
"Most purchased product" would require a definition. It could represent 1) frequency of purchase, 2) total currency value of purchases, 3) largest numbers of unique purchasers and likely a few more ideas depending on what the "product" concerns.
as a minimum a description of your data would help provide more targeted suggestions, best is some example data in the form of a data step.
Also, do you want/need a data step to provide input to a model or further data processing or do you want a report that people will read?
I would likely look at starting with Proc summary with your country and product variables as CLASS variables and what ever you are using to measure "most" or "least" as VAR variable that gets summed. I would likely use the NWAY option as well unless I wanted sums of the VAR variables overall, by country regardless of product and by product regardless of country.
data new;
input productid $ country $ quantity 5.;
datalines;
22139 UK 56
21773 UK 4
21773 UK 5
21801 UK 34
21056 SA 1
21773 SA 34
34788 MN 345
3456 MN 234
;
I need table given below:
title "most purchased product"
country productid maxqty(as per quantity)
UK 22139 56
SA 21773 34
MN 34788 345
similarly for least purchased product
didn't got the same output.
kindly help
Try
proc sql;
create table want as
select country, productid, max(quantity) as maxqty
from new
group by country
having quantity = max(quantity);
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.