## Assign max count in groups

Solved
Frequent Contributor
Posts: 84

# Assign max count in groups

Hi ,

I have a data like this

data have;

input ID Group\$ Count;

cards;

101 A 180

101 B 90

101 C 60

102 A 90

102 B 60

102 C 50

103 A 60

103 B 50

103 C 70

run;

I want this as output:

 ID Group 101 A 102 B 103 C

I want to assign group for id's which has max count. lets take 101 it has max count in group A. so assign A to 101. Now coming to 102 it is also having max count in group A, but A is already assigned to 101 , here i want to assign second highest max count to 102 i.e. B .for 103 it's simple i.e C..... so on..

Thanks..

Accepted Solutions
Solution
‎06-03-2015 08:07 AM
Super User
Posts: 10,784

## Re: Assign max count in groups

data have;

input ID Group\$ Count;

cards;

101 A 180

101 B 90

101 C 60

102 A 90

102 B 60

102 C 50

103 A 60

103 B 50

103 C 70

;

run;

proc sort data=have;by id descending Count;run;

data want;

set have;

by id;

array key{99999} \$ 32 _temporary_;

retain found;

if first.id then found=.;

drop found n;

run;

Xia Keshan

All Replies
Super User
Posts: 10,271

## Re: Assign max count in groups

proc sort data=have out=wantx;

by id count;

run;

data want (keep=id group);

set wantx;

by id;

if last.id;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 84

## Re: Assign max count in groups

No that is wrong . This will give A group twice. if the group is already assigned the i want second highest group to be assigned.

Super User
Posts: 8,116

## Re: Assign max count in groups

So sort by GROUP instead of ID.

What do you want to do if the same ID is the max for more than one group?

Frequent Contributor
Posts: 84

## Re: Assign max count in groups

If the ID is max in more than one group assign the id's in ascending order or sequential or first come first assigned.

Thanks..

Posts: 5,535

## Re: Assign max count in groups

So, if you reorder the IDs, you will different group assignments?

PG
Valued Guide
Posts: 864

## Re: Assign max count in groups

You have to remove the max group once it is used.  I've been looking at this hoping someone will show the solution because I don't know how to do it.

Posts: 5,535

## Re: Assign max count in groups

Then, what is the group assignment for a given ID when all associated groups have already been assigned?

PG
Valued Guide
Posts: 864

## Re: Assign max count in groups

The last id would be the last group available.

Frequent Contributor
Posts: 84

## Re: Assign max count in groups

i think yes.. one group is assigned to one id only that is main condition after max count.

Posts: 5,535

## Re: Assign max count in groups

You could use an array to keep track of assigned groups:

proc sort data=have; by ID descending Count; run;

data want;

array grp{1000} \$8 _temporary_;

length AssignedGroup \$8;

do until (last.ID);

set have; by ID;

if missing(AssignedGroup) then

if Group not in grp then do;

AssignedGroup = Group;

output;

n + 1;

grp{n} = Group;

end;

end;

keep ID AssignedGroup;

run;

PG

PG
Posts: 3,167

## Re: Assign max count in groups

Similar to PG's solution, only use Hash for more dynamic approach:

data have;

input ID Group\$ Count;

cards;

101 A 180

101 B 90

101 C 60

102 A 90

102 B 60

102 C 50

103 A 60

103 B 50

103 C 70

run;

proc sort data=have out=have1;

by id  descending count;

run;

data want;

if _n_=1 then

do;

dcl hash h();

h.definekey('group');

h.definedone();

end;

do until (last.id);

set have1;

by id  descending count;

_r=h.check();

if _f ne 1 and _r ne 0 then

do;

_f=1;

_r=h.replace();

_g=group;

_c=count;

end;

end;

group=_g;

count=_c;

drop _:;

run;

Super User
Posts: 10,784

## Re: Assign max count in groups

```
data have;
input ID Group\$ Count;
cards;
101 A 180
101 B 90
101 C 60
102 A 90
102 B 60
102 C 50
103 A 60
103 B 50
103 C 70
;
run;
proc sort data=have;by id descending Count;run;
data want;
if _n_ eq 1 then do;
if 0 then set have;
declare hash h();
h.definekey('Group');
h.definedone();
end;
set have;
by id;
retain found;
if first.id then found=.;
drop found;
run;

```

Xia Keshan

Valued Guide
Posts: 864

## Re: Assign max count in groups

This seems like a good venue for this question.  If I was going to pick up a book... the next thing I am going to study is ()

a)  hash

b)  array

c)  DS2

Valued Guide
Posts: 864

## Re: Assign max count in groups

The question is to the people that had the solution.  For someone that couldn't come up with the solution, which between a b c would you recommend for my next undertaking.  I suppose it's a weird question because everyone is going to have their 'home' or favorite, is there one that's a good starting point, or one that stands above the rest?  The question doesn't reference a specific goal or function, just general programming knowledge.

I've never logged into this after a few drinks... this is why

🔒 This topic is solved and locked.