DATA Step, Macro, Functions and more

Assign max count in groups

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

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:

IDGroup
101A
102B
103C

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: 9,691

Re: Assign max count in groups

Use array instead.

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=.;

if not found and group not in key then do;output;n+1;key{n}=group;found=1;end;

drop found n;

run;

Xia Keshan

View solution in original post


All Replies
Super User
Posts: 6,972

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
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
Super User
Posts: 6,502

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..

Respected Advisor
Posts: 4,660

Re: Assign max count in groups

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

PG
Valued Guide
Posts: 858

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.

Respected Advisor
Posts: 4,660

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: 858

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.

Respected Advisor
Posts: 4,660

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
Respected Advisor
Posts: 3,124

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: 9,691

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=.;
 if not found and h.check() ne 0 then do;output;h.add();found=1;end;
 drop found;
run;




Xia Keshan

Valued Guide
Posts: 858

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: 858

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 Smiley Wink

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 670 views
  • 11 likes
  • 8 in conversation