BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kumarK
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

20 REPLIES 20
kumarK
Quartz | Level 8

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.

Tom
Super User Tom
Super User

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?

kumarK
Quartz | Level 8

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

PGStats
Opal | Level 21

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

PG
Steelers_In_DC
Barite | Level 11

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.

PGStats
Opal | Level 21

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

PG
Steelers_In_DC
Barite | Level 11

The last id would be the last group available.

kumarK
Quartz | Level 8

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

PGStats
Opal | Level 21

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
Haikuo
Onyx | Level 15

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;

Ksharp
Super User

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

Steelers_In_DC
Barite | Level 11

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

Steelers_In_DC
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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