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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.