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..
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
proc sort data=have out=wantx;
by id count;
run;
data want (keep=id group);
set wantx;
by id;
if last.id;
run;
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.
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?
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..
So, if you reorder the IDs, you will different group assignments?
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.
Then, what is the group assignment for a given ID when all associated groups have already been assigned?
The last id would be the last group available.
i think yes.. one group is assigned to one id only that is main condition after max count.
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
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;
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
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
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
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.