|
|
Re: 请教高手：多列带有重复的ID合并成一列
没啥好法子,只能寄希望于重复的ID尽可能的多
[code:1bj53880]data raw;
input id1 id2 id3 id4;
cards;
1 11 21 31
1 12 22 31
2 11 22 32
2 13 21 32
3 14 23 32
4 11 22 33
55 56 57 58
;
proc sql;
create table id_pair as
select id1 as old_id,id1 as new_id from raw
union
select id2 as old_id,id2 as new_id from raw
union
select id3 as old_id,id3 as new_id from raw
union
select id4 as old_id,id4 as new_id from raw;
quit;
data _null_;
if _n_=1 then do;
declare hash h(dataset:"id_pair",ordered:'yes',hashexp:16);
h.defineKey('old_id');
h.defineData('old_id','new_id');
h.defineDone();
call missing(old_id,new_id);
declare hiter iter('h');
end;
set raw end=eof;
array arr{*} id1-id4;
do i=1 to 3;
do j=i+1 to 4;
rc=h.find(key:arr(i));
temp=new_id;
do while(iter.next()=0);
if new_id=temp then rc=h.replace(key:old_id,data:old_id,data:arr(j));
end;
end;
end;
if eof then rc=h.output(dataset:'_temp');
run;
proc sql;
create table id_pair as
select a.old_id,b.group
from _temp a,
(select min(old_id) as group,old_id from _temp
group by new_id) b
where a.old_id=b.old_id;
drop table _temp;
quit;
data final(keep=group id1-id4);
if _n_ = 1 then do;
declare hash h(dataset:"id_pair",hashexp:16);
h.defineKey('old_id');
h.defineData('group');
h.defineDone();
call missing(old_id,group);
end;
set raw;
rc=h.find(key:id1);
run;[/code:1bj53880] |
|
楼主
