请教高手：多列带有重复的ID合并成一列

shiyiming

Here is my two cents. Haven't got the chance to test it on huge datasets though.  <!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->

[code:297rq356]data a1;
  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
5 6 7 8
5 0 0 0
4 20 30 40
9 4 3 8
10 0 5 6
100 100 100 100
;
run;

data a1;
  set a1;
  rowid = _n_;
run;

proc sql;
  create table matches as
    select a&#46;rowid as rowid_a,
           a&#46;id1 as id1_a,
           a&#46;id2 as id2_a,
           a&#46;id3 as id3_a,
           a&#46;id4 as id4_a,
           b&#46;rowid as rowid_b,
           b&#46;id1 as id1_b,
           b&#46;id2 as id2_b,
           b&#46;id3 as id3_b,
           b&#46;id4 as id4_b
      from a1 as a,
           a1 as b
      where a&#46;rowid ^= b&#46;rowid and
            (a&#46;id1 = b&#46;id1 or
             a&#46;id2 = b&#46;id2 or
             a&#46;id3 = b&#46;id3 or
             a&#46;id4 = b&#46;id4
            );
quit;

proc sql;
  create table matches as
    select distinct
           1 as id,
           min(rowid_a, rowid_b) as rowid1,
           max(rowid_a, rowid_b) as rowid2
      from matches;

  create table matches as
    select id,
           rowid1,
           rowid2
      from matches

    union all

    select 1 as id,
           a&#46;rowid as rowid1,
           a&#46;rowid as rowid2
      from a1 as a
           left join
           matches as b
           on a&#46;rowid = b&#46;rowid1 or
              a&#46;rowid = b&#46;rowid2
      where b&#46;rowid1 is null;
quit;

data results(keep = rowsetid rowset);
  set matches;
  by id;

  retain rowsetid rowset newset;
  length rowset $256;  /* Adjust this length if necessary&#46; */

  if first&#46;id then do;
    rowsetid = 1;
    newset = 1;
  end;

  if newset = 1 then do;
    rowset = ', ' || strip(put(rowid1, best&#46;)) || ',';
    if index(rowset, ', ' || strip(put(rowid2, best&#46;)) || ',') = 0 then rowset = strip(rowset) || ' ' || strip(put(rowid2, best&#46;)) || ',';
    newset = 0;
  end; else do;
    if index(rowset, ', ' || strip(put(rowid1, best&#46;)) || ',') &gt; 0  and index(rowset, ', ' || strip(put(rowid2, best&#46;)) || ',') = 0 then
      rowset = strip(rowset) || ' ' || strip(put(rowid2, best&#46;)) || ','; else
    if index(rowset, ', ' || strip(put(rowid2, best&#46;)) || ',') &gt; 0  and index(rowset, ', ' || strip(put(rowid1, best&#46;)) || ',') = 0 then
      rowset = strip(rowset) || ' ' || strip(put(rowid1, best&#46;)) || ','; else
    if index(rowset, ', ' || strip(put(rowid1, best&#46;)) || ',') = 0  and index(rowset, ', ' || strip(put(rowid2, best&#46;)) || ',') = 0 then do;
      rowset = substr(rowset, 3, length(rowset) - 3);
      output;
      rowsetid = rowsetid + 1;
      rowset = ', ' || strip(put(rowid1, best&#46;)) || ',';
      if index(rowset, ', ' || strip(put(rowid2, best&#46;)) || ',') = 0 then rowset = strip(rowset) || ' ' || strip(put(rowid2, best&#46;)) || ',';
    end;
  end;

  if last&#46;id then do;
    rowset = substr(rowset, 3, length(rowset) - 3);
    output;
  end;
run;[/code:297rq356]

shiyiming

[color=#0000FF:2ua7mg36]to cloudpan2002:[/color:2ua7mg36]

你的第一个SQL好像就要RUN很长时间........................

shiyiming

猪头，有bug，快改啊。
我用这个数据集测试了一下下，有误。

data a1;
input id1 id2 id3 id4;
cards;
1 11 21 31
1 11 21 31
1 12 22 31
1 12 22 31
2 13 21 32
2 11 22 32
3 14 23 32
4 11 22 33
4 11 25 33
7 8  9  11
7 10 11 12
7 10 53 51
100  50  60 101
100  20  30 102
100  40  50 103
102  105 108 109
;
run;

shiyiming

下面这段代码，只能把多维变成2维的问题这一步，好像就是猪头说的“所有的边放进一个两列（假设为x, y，记录每条边的两个端点）的表”。
同一条观察的num1，num2值分别代表可以为同一组的两个原顺序号,估计类似x，y，不大懂。

后面的需要 索引，搜索什么的，就不会了。

ps：代码吃CPU，慎试。

[code:22z0j8g2]
libname tem 'd&#58;\';

data tem&#46;a1;
   array id&#91;1&#58;4&#93;;
   do _n_=1 to 700000;
      do _i_ = 1 to dim(id);
         id&#91;_i_&#93; = ceil(20000*ranuni(12345));
      end;
      call sortn(of id&#91;*&#93;);        num1+1;
   drop _i_;      output;
   end;

run;
data tem&#46;ex1;
   array ids &#91;4&#93; id1-id4;
  set tem&#46;a1;
     do i=1 to 4;
      id=ids&#91;i&#93;;
          if i=5 then num1+1;
          num2=num1;
     keep id num2;
     output;
        end;
run;

data tem&#46;ex2;
     set tem&#46;a1 (rename=(id1=cid1 id2=cid2 id3=cid3 id4=cid4)) nobs=max;
     array cid &#91;4&#93; cid1-cid4;
         put _n_;
     do i=1 to max*4;
           set tem&#46;ex1 point=i;
                      if mod(i,4)=1 then  sum=0;
              do j=1 to 4;
                if num1 ne num2 then  sum+(id=cid&#91;j&#93;);
              end;

                          if mod(i,4)=0 then
                 do;
                    if sum ge 2  then
                                          do;
                                                 call sortn(num1,num2);
                                             keep num1 num2;
                         output;
                      end;
                 end;
      end;

run;

proc sort  data=tem&#46;ex2 out=tem&#46;ex2 nodupkey;
    by num1 num2;
run;[/code:22z0j8g2]

shiyiming

[quote:1iv88k7v]to cloudpan2002:

你的第一个SQL好像就要RUN很长时间........................
[/quote:1iv88k7v]

Yes this is very possible when the data gets huge. Looking forword to better solutions.  <!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->

shiyiming

<!-- s:? --><img src="{SMILIES_PATH}/icon_confused.gif" alt=":?" title="Confused" /><!-- s:? -->

shiyiming

谢谢sxlion纠错！你简直比我导师还厉害！ahuige不是说你以前做过这个吗？能抖抖你的答案吗？
终于领会了oloolo的想法，真是高瞻远瞩啊！

shiyiming

猪头，我什么也不懂，只能玩千条以内的记录。

这个问题，初一看，与以前讨论的问题有点像，结果认真一看，形似神不似，以前的问题简单多了。

数据太多，上面构造的边太多，cpu发烧。 看来70万×4的想法很有优势，就是1000万估计也没问题，不过怎么构造这些边和进行搜索操作呢？ 这已经超过了我的认知范围。

还请继续讨论，让我学习学习。

shiyiming

工作中的实际问题。课后练习题的话，也不可能七十多万个record ：）
看来得找那些专业研究算法的高手了 ：）
（非常感谢楼上大侠们的idea，虽然还没解决问题，但眼界开阔不少 ：））

shiyiming

to 死猪头
right, at first look, it looks like a typical transverse problem in a graph, however, it is more sophisticated, but the same principle applies. You still can do a DFS-type search which is not hard to code in SAS.

I just found this is an open issue in OP's work, and unless he pays me....