DATA Step, Macro, Functions and more

How to locate a minimum value by group

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

How to locate a minimum value by group

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.


Accepted Solutions
Solution
‎03-27-2012 03:41 PM
Respected Advisor
Posts: 3,124

How to locate a minimum value by group

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,651

How to locate a minimum value by group

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

PG
N/A
Posts: 1

How to locate a minimum value by group

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;

Solution
‎03-27-2012 03:41 PM
Respected Advisor
Posts: 3,124

How to locate a minimum value by group

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;

Respected Advisor
Posts: 4,651

How to locate a minimum value by group

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

PG
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 5264 views
  • 8 likes
  • 4 in conversation