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.
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;
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
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;
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.