turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- How to locate a minimum value by group

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-27-2012 03:07 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to tesu

03-27-2012 03:41 PM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to tesu

03-27-2012 03:36 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

03-27-2012 04:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to tesu

03-27-2012 03:41 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to tesu

03-27-2012 04:21 PM

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