Turn on suggestions

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

Showing results for

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

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-27-2012 03:07 PM
(11423 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

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.