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

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

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

4 REPLIES 4
PGStats
Opal | Level 21

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
Joel_d_w
Calcite | Level 5

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

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
Opal | Level 21

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

Ready to join fellow brilliant minds for the SAS Hackathon?

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

Register today!
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
  • 4 replies
  • 11424 views
  • 8 likes
  • 4 in conversation