Get rid of the duplicated 2s and run @Tom 's code again.
data have;
input sno seqnum codenum dnum b$;
datalines;
1 1 100 2 a
1 2 100 3 b
1 5 100 3 c
1 6 100 3 d
1 4 200 2 e
1 5 200 2 f
1 6 200 3 g
1 7 200 3 h
2 3 150 2 i
2 5 150 3 j
2 6 150 4 k
2 1 250 2 l
2 2 250 3 m
2 1 300 2 n
3 11 200 2 o
3 12 200 3 p
3 13 200 3 q
3 15 200 4 r
3 10 250 2 s
;
run;
data temp more;
set have;
by sno codenum dnum ;
if dnum=2 and not last.dnum then output more;
else output temp;
run;
proc sql ;
create table want1 as
select a.sno,a.codenum,a.dnum as dnum1,b.dnum as dnum2,a.b as b1 ,b.b as b2
from
temp(where=(dnum=2)) as a
left join temp(where=(dnum=3)) as b
on a.sno = b.sno and a.codenum=b.codenum ;
create table want2 as
select a.sno,a.codenum,max(dnum1,dnum2,b.dnum) as dnum
, cats(b1,b2,b.b) as xlist
from
want1 as a
left join temp(where=(dnum=4)) as b
on a.sno = b.sno and a.codenum=b.codenum
group by 1,2
order by 1,2
;
quit;
data want;
set want2 more(keep= sno codenum dnum b rename=(b=xlist));
by sno codenum dnum ;
run;
... View more
