topic Re: Assign max count in groups in SAS Programming

Assign max count in groups

kumarK — Thu, 21 May 2015 06:11:06 GMT

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

Re: Assign max count in groups

Kurt_Bremser — Thu, 21 May 2015 06:49:14 GMT

proc sort data=have out=wantx;

by id count;

run;

data want (keep=id group);

set wantx;

by id;

if last.id;

run;

Re: Assign max count in groups

kumarK — Thu, 21 May 2015 18:36:42 GMT

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.

Re: Assign max count in groups

PGStats — Thu, 21 May 2015 18:57:02 GMT

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

Re: Assign max count in groups

Steelers_In_DC — Thu, 21 May 2015 19:14:30 GMT

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.

Re: Assign max count in groups

kumarK — Thu, 21 May 2015 19:24:02 GMT

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

Re: Assign max count in groups

PGStats — Thu, 21 May 2015 19:28:22 GMT

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

Re: Assign max count in groups

Steelers_In_DC — Thu, 21 May 2015 19:31:04 GMT

The last id would be the last group available.

Re: Assign max count in groups

PGStats — Thu, 21 May 2015 19:54:04 GMT

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;

keep ID AssignedGroup;

run;

Re: Assign max count in groups

Haikuo — Thu, 21 May 2015 20:09:53 GMT

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;

group=_g;

count=_c;

drop _:;

run;

Re: Assign max count in groups

Ksharp — Fri, 22 May 2015 12:29:31 GMT


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

Re: Assign max count in groups

Steelers_In_DC — Fri, 22 May 2015 14:46:59 GMT

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

Re: Assign max count in groups

Steelers_In_DC — Fri, 22 May 2015 15:37:58 GMT

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

Re: Assign max count in groups

kumarK — Tue, 02 Jun 2015 18:33:54 GMT

Thanks all.

I am still confused to use HASH

Re: Assign max count in groups

kumarK — Tue, 02 Jun 2015 18:39:48 GMT

Hi,

Can anyone help me to understand this code.?

Thanks..

Re: Assign max count in groups

Tom — Tue, 02 Jun 2015 18:57:50 GMT

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?

Re: Assign max count in groups

Tom — Tue, 02 Jun 2015 19:08:36 GMT

Not sure this is guaranteed to find all of the groups, but it sounds like what you requested.

%macro loop;

proc sql ;

%let nobs=0;

%do %until (&nobs=0) ;

create table next as

select * from have

where group not in (select group from want)

having count = max(count)

order by count desc

;

%let nobs=&sqlobs ;

insert into want select * from next(obs=1);

%end;

quit;

%mend loop;

data want; set have (obs=0); run;

%loop;

Re: Assign max count in groups

ballardw — Tue, 02 Jun 2015 19:31:15 GMT

If I wasn't comfortable with arrays that's where I would start.

For one thing the concept is around in very many programming languages so you can apply the skills learned in SAS elsewhere OR use examples written for other languages once you know the general syntax differences.

Re: Assign max count in groups

kumarK — Wed, 03 Jun 2015 04:54:05 GMT

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

Re: Assign max count in groups

kumarK — Wed, 03 Jun 2015 04:59:13 GMT

the code is not considering all the id's.

Xia and Hai.Kuo 's code is giving correct results but the problem is i didn't understand the code what is happening there, this leads to confusion in replicate the code according to my original scenario...

Thanks.