I think this question is a hard one.
group x xnum
a ui 100
b gg 100
c hy 40
c kt 59
c jj 1
d kq 88
d qp 3
d qv 9
e nl 100
f aq 34
f zz 2
f bb 30
f mq 34
g qq 66
g gt 5
g zf 29
h hu 100
i aa 40
i xf 20
i ir 40
j er 33
j ae 33
j vt 33
Here, I have to select observation(s) that the value(s) of xnum is(are) maximum by group. One more thing to consider!: Look at the group of f,i, and j. The group f and i have two maximum values in each group in xnum, and the group j has three maximum values in xnum. So, the final output data should be as follows:
group x xnum
a ui 100
b gg 100
c kt 59
d kq 88
e nl 100
f aq/mq 34
g qq 66
h hu 100
i aa/ir 40
j er/ae/vt 33
I wonder whether or not any SAS expert can solve this problem. Thanks.
One way to go:
data have;
infile datalines dlm=' ' truncover;
input (group x) (:$2.) xnum 8.;
datalines;
a ui 100
b gg 100
c hy 40
c kt 59
c jj 1
d kq 88
d qp 3
d qv 9
e nl 100
f aq 34
f zz 2
f bb 30
f mq 34
g qq 66
g gt 5
g zf 29
h hu 100
i aa 40
i xf 20
i ir 40
j er 33
j ae 33
j vt 33
;
run;
proc sql;
create view v_inter as
select
group,
x as _x,
max(xnum) as max_xnum
from have
group by group
having max(xnum)=xnum
;
quit;
data want(drop=_:);
set v_inter;
by group;
length x $30;
retain x;
x=catx('/',x,_x);
if last.group then
do;
output;
call missing(x);
end;
run;
One way to go:
data have;
infile datalines dlm=' ' truncover;
input (group x) (:$2.) xnum 8.;
datalines;
a ui 100
b gg 100
c hy 40
c kt 59
c jj 1
d kq 88
d qp 3
d qv 9
e nl 100
f aq 34
f zz 2
f bb 30
f mq 34
g qq 66
g gt 5
g zf 29
h hu 100
i aa 40
i xf 20
i ir 40
j er 33
j ae 33
j vt 33
;
run;
proc sql;
create view v_inter as
select
group,
x as _x,
max(xnum) as max_xnum
from have
group by group
having max(xnum)=xnum
;
quit;
data want(drop=_:);
set v_inter;
by group;
length x $30;
retain x;
x=catx('/',x,_x);
if last.group then
do;
output;
call missing(x);
end;
run;
Here is another:
proc sort data=have;
by group descending xnum;
run;
data want(keep=group--xmax);
length group $1 xxx $100 xmax 8;
do until(last.group);
set have;
by group;
if first.group then xmax=xnum;
if xnum=xmax then xxx=catx("/",xxx,x);
end;
run;
And a one-step-solution.
data _null_;
length group $ 2
xList $ 200
max 8
;
if _n_ = 1 then do;
declare hash h(ordered: 'yes');
h.defineKey('group');
h.defineData('group', 'xList', 'max');
h.defineDone();
call missing(group, xList, max);
end;
set work.have end= jobDone;
if h.find() ^= 0 then do;
/* new group */
max = xnum;
xList = x;
h.add();
end;
else do;
if max = xnum then do;
/* adding x to the group */
xList = catx('/', xList, x);
h.replace();
end;
if max < xnum then do;
/* replacing small xnum */
max = xnum;
xList = x;
h.replace();
end;
end;
if jobDone then do;
h.output(dataset: 'work.want(rename=(xList = x max = xnum))');
end;
run;
Hi... Pls the try following code:
data have;
infile datalines dlm=' ' truncover;
input (groupname x) (:$2.) xnum 8.;
datalines;
a ui 100
b gg 100
c hy 40
c kt 59
c jj 1
d kq 88
d qp 3
d qv 9
e nl 100
f aq 34
f zz 2
f bb 30
f mq 34
g qq 66
g gt 5
g zf 29
h hu 100
i aa 40
i xf 20
i ir 40
j er 33
j ae 33
j vt 33
;
run;
proc sql;
create table temp_table as(select groupname, x , xnum from have a where xnum=(select max(xnum) from have b group by groupname having a.groupname=b.groupname));
quit;
proc sort data=temp_table; by groupname xnum; run;
data want;length b1 $20.; retain b1;
set temp_table; by groupname xnum;
if first.xnum then b1=x;
else b1=catt(b1,',',x);
if last.xnum;
drop x; rename b1=x;
run;
Surprised that nobody mentioned the classical 2XDOW approach:
data have;
infile datalines dlm=' ' truncover;
input (group x) (:$2.) xnum 8.;
datalines;
a ui 100
b gg 100
c hy 40
c kt 59
c jj 1
d kq 88
d qp 3
d qv 9
e nl 100
f aq 34
f zz 2
f bb 30
f mq 34
g qq 66
g gt 5
g zf 29
h hu 100
i aa 40
i xf 20
i ir 40
j er 33
j ae 33
j vt 33
;
run;
data want;
do until (last.group);
set have;
by group;
length list $ 100;
max=max(max,xnum);
end;
do until (last.group);
set have;
by group;
if xnum=max then list=catx('/',list,x);
end;
drop x xnum;
run;
Haikuo
Thank you, all. You guys are amazing!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.