How to locate a minimum value by group

[tesu]

Hi all,

data min_ex;

input g $ x;

cards;

g1 4

g2 3

g2 7

g2 2

g3 8

g4 10

g4 2

g4 9

g4 7

g5 9

g6 1

g6 8

g6 2

g6 9

;run;

I have to create a variable(say, min_x_by_g) which indicates that if the value of x is a minimum within the group g, the min_x_by_g has the value of 1, otherwise 0. Plus, if there is only one observation within a group (for example, g1, g3, and g5 have one observation each.), the min_x_by_g variable has to have the value of 2.

How can I do this?

Thanks.

[PGStats]

Try :

proc sql;

create table min_x_by_gs as

select g, x, (x=min(x))+(count(x)=1) as min_x_by_g

from min_ex

group by g;

select * from min_x_by_gs;

quit;

PG

[Joel_d_w]

I actually like PG's better, but here's a similar approach using a case instead.

proc sql; create table test as

select g, x,

case

          when count(x)=1 then 2

          when min(x)=x then 1

          else 0

end as min_x_by_g

from min_ex

group by g;

quit;

[Haikuo · Accepted solution]

If you don't care about the order:

data min_ex;

input g $ x;

cards;

g1 4

g2 3

g2 7

g2 2

g3 8

g4 10

g4 2

g4 9

g4 7

g5 9

g6 1

g6 8

g6 2

g6 9

;run;

proc sort data=min_ex out=have;

by g x;

run;

data want;

set have;

by g x;

if first.g and last.g then min_x_by_g=2;

else if first.g then min_x_by_g=1;

else min_x_by_g=0;

run;

proc print;run;

[PGStats]

Actually, if you do want to preserve the original dataset order then you must do your own remerge as in :

proc sql;

create table min_x_by_gs as

select M.g, x, (x=minX)+(n=1) as min_x_by_g

from min_ex as M inner join

(select g, min(x) as minX, count(*) as n from min_ex group by g) as S

on M.g=S.g;

select * from min_x_by_gs;

quit;

try adding "g1 3" as the last line of min_ex to see the difference.

PG