topic Re: Hard question: Selecting observation(s) conditionally by group in SAS Programming

Hard question: Selecting observation(s) conditionally by group

tesu — Thu, 12 Dec 2013 07:05:09 GMT

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.

Re: Hard question: Selecting observation(s) conditionally by group

Patrick — Thu, 12 Dec 2013 08:13:16 GMT

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;

Re: Hard question: Selecting observation(s) conditionally by group

barchan — Thu, 12 Dec 2013 08:58:49 GMT

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;

Re: Hard question: Selecting observation(s) conditionally by group

andreas_lds — Thu, 12 Dec 2013 15:22:59 GMT

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;

if jobDone then do;

h.output(dataset: 'work.want(rename=(xList = x max = xnum))');

end;

run;

Re: Hard question: Selecting observation(s) conditionally by group

Kavitha — Thu, 12 Dec 2013 17:59:44 GMT

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;

Re: Hard question: Selecting observation(s) conditionally by group

Haikuo — Thu, 12 Dec 2013 18:41:53 GMT

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

Re: Hard question: Selecting observation(s) conditionally by group

tesu — Thu, 12 Dec 2013 20:35:43 GMT

Thank you, all. You guys are amazing!