topic Re: select most frequent character (i.e., country) in each group (i.e. company) in SAS Programming

select most frequent character (i.e., country) in each group (i.e. company)

France — Thu, 29 Nov 2018 17:15:15 GMT

Dear all,

how to chose country character which record most frequently with the company.

for example

country, company
US, apple
US, apple
GB, apple
US, apple
CH, pear
CH, pear
US, pear

I would like the result looks like

country, company
US, apple
CH, pear

Could you please give me some suggestion about it?

thanks in adanvce

Re: select most frequent character (i.e., country) in each group (i.e. company)

ballardw — Thu, 29 Nov 2018 17:26:03 GMT

If I understand then something like this perhaps:

proc freq data=have noprint;
   table company*country/out=work.temp(drop=percent);
run;

proc sort data=work.temp;
  by company descending count;
run;

data want;
  set work.temp;
  by company;
  if first.company;
run;

is just one way.

Re: select most frequent character (i.e., country) in each group (i.e. company)

novinosrin — Thu, 29 Nov 2018 18:11:02 GMT

data have;
infile cards dsd truncover;
input (country company) (:$10.);
cards;
US, apple
US, apple
GB, apple
US, apple
CH, pear
CH, pear
US, pear
;

proc sql;
create table want(drop=c) as
select *
from (select company,country, count(company) as c from have group by company,country)
group by company
having c=max(c);
quit;