[求助] 关于筛选相同记录的问题

shiyiming

各位大侠：
    如果在一个dataset中存在两条或多条相同记录，该如何将他们选出来。
    比如：
dataset: allobs中，含 a b c d e.........等多列。
我用proc sql写：
proc sql;
create table sameobs as select * from allobs
group by a, b, c, d, e ........
having count(a)>1;

我想知道，如果不用sql，用proc summary 怎么写？以前看到有人用proc summay写，忘了具体内容了。
bow！

shiyiming

I am not sure if there is any advantage to use proc summary, but the most fundamental way to do it is the following:

proc sort data=allobs;
by a b c d e;
run;

data ck;
set allobs;
by a b c d e;
if first.e+last.e ne 2;
run;

shiyiming

谢谢 xic。
你总是最快的回复。谢谢你！

shiyiming

和你的SQL相对应的proc summary 写法可以是：

proc summary data=allobs nway;
class a b c d e;
output out=someobs(where=(_freq_>1));
run;

使用Proc Summary代替SQL的理由是，在很多情形下的测试表明 summary要比SQL快，做个简单实验：
生成一个含32种重复记录的表（总共5000000条记录）
[code:b6e99]%let totalSize = 5000000;

%let DupRate = 0.8;

data total;
array v(*) v1 - v5;
do i=1 to &totalSize;
   do j=1 to dim(v);
      v(j) = 100 + 2 - rantbl(-1, (1 - &DupRate ));
   end;
   output;
end;
drop i j;
run;[/code:b6e99]

用summary选出32种重复记录，5秒多
[code:b6e99]proc summary data=total nway;
class v1 - v5;
output out=out2(drop=_type_ where=(_freq_>1));
run;[/code:b6e99]

用sql，在有cache便宜可占的情况下，还用了4分钟
[code:b6e99]proc sql;
create table out3
as
select *
from total
group by v1,v2,v3,v4,v5
having count(v1)>1;
quit;[/code:b6e99]

但是上述方法只能得到重复的变量组合，而xic 的方法可以提供更为详细的重复记录信息，并且是逐条输出的。

shiyiming

有辛在SASOR文档库上看到有人提交了这篇文章。自己试了一下，PROC SUMMARY的速度的确快，但是5s肯定有问题，不可能这么快。SQL也不会这么慢。

附上我的日志：
[code:92c81]103  %let totalSize = 5000000;
104  %let DupRate = 0.8;
105  data total;
106  array v(*) v1 - v5;
107  do i=1 to &totalSize;
108  option fullstimer msglevel=i;
109  do j=1 to dim(v);
110  v(j) = 100 + 2 - rantbl(-1, (1 - &DupRate ));
111  end;
112  output;
113  end;
114  drop i j;
115  run;

NOTE: 数据集 WORK.TOTAL 有 5000000 个观测和 5 个变量。
NOTE:  压缩的数据集 WORK.TOTAL 大小减少了百分之 17.39。
       压缩的是 40897 页；未压缩的要求 49506页。
NOTE: DATA 语句占用：
      实际时间         1:08.29
      CPU 时间         1:07.89


116
117  proc summary data=total nway;
118  class v1 - v5;
119  output out=out2(drop=_type_ where=(_freq_>1));
120  run;

NOTE: 有 5000000 个观测从数据集 WORK.TOTAL. 中读取
NOTE: 数据集 WORK.OUT2 有 32 个观测和 6 个变量。
NOTE:  压缩的数据集 WORK.OUT2 大小减少了百分之 100.00。
       压缩的是 2 页；未压缩的将要求 1页。
NOTE: PROCEDURE SUMMARY占用：
      实际时间         19.79 秒
      用户 CPU 时间     18.85 秒
      系统 CPU 时间     0.92 秒
       内存                            129k


121
122  proc sql;
123  create table out3
124  as
125  select *
126  from total
127  group by v1,v2,v3,v4,v5
128  having count(v1)>1;
NOTE:  压缩的数据集 WORK.OUT3 大小减少了百分之 100.00。
       压缩的是 2 页；未压缩的将要求 1页。
NOTE: Table WORK.OUT3 created, with 32 rows and 5 columns.

129  quit;
NOTE: PROCEDURE SQL占用：
      实际时间         51.09 秒
      用户 CPU 时间     44.28 秒
      系统 CPU 时间     6.53 秒
       内存                            417158k
[/code:92c81]

机器是1.6G Xeon *2。运行时还有其他任务在跑。

shiyiming

感谢Sharwyn腾出看家的服务器来测，并且让这个游戏玩得热闹。
我在笔记本和台式机上各做了几次试验，典型的结果是：

[code:18996]
1    options fullstimer msglevel=i;
2
3    %let totalSize = 5000000;
4
5    %let DupRate = 0.8;
6
7    data total;
8    array v(*) v1 - v5;
9    do i=1 to &totalSize;
10      do j=1 to dim(v);
11           v(j) = 100 + 2 - rantbl(-1, (1 - &DupRate ));
12      end;
13      output;
14   end;
15   drop i j;
16   run;

NOTE: The data set WORK.TOTAL has 5000000 observations and 5 variables.
NOTE: DATA statement used:
      real time           33.93 seconds
      user cpu time       31.43 seconds
      system cpu time     1.65 seconds
      Memory                            96k


17
18   proc summary data=total nway;
19   class v1 - v5;
20   output out=out2(drop=_type_ where=(_freq_>1));
21   run;

NOTE: There were 5000000 observations read from the data set WORK.TOTAL.
NOTE: The data set WORK.OUT2 has 32 observations and 6 variables.
NOTE: PROCEDURE SUMMARY used:
      real time           7.07 seconds
      user cpu time       6.29 seconds
      system cpu time     0.43 seconds
      Memory                            121k


22
23   proc sql;
24   create table out3
25   as
26   select *
27   from total
28   group by v1,v2,v3,v4,v5
29   having count(v1)>1;
NOTE: Table WORK.OUT3 created, with 32 rows and 5 columns.

30   quit;
NOTE: PROCEDURE SQL used:
      real time           3:13.98
      user cpu time       20.98 seconds
      system cpu time     4.21 seconds
      Memory                            417157k
[/code:18996]

和Sharwyn讨论了一下，有以下猜想：
1。本例中summary胜出是无疑的
2。summary的算法在此情形中比较优化，CPU时间少，内存使用少，IO操作少，却完成了相同的任务；SQL在这样的例子中内存居然夸张的用到了417M
3。我的测试中，summary比Sharwyn快的原因是CPU快，而SQL慢在物理内存少和硬盘IO低


测试机
笔记本 1.4G迅驰，内存512M DDR333
台式机 2.0G P4，内存512M DDR266

shiyiming

我的结果和结论与SAS_Dream非常相似，机器刚重起，没有其他作业。
我把SQL和SUMMARY的位置对调了，结果还是一样。
运行完程序后,现在机器变得暴慢！
[code:72d04]1    options fullstimer msglevel=i;
2    %let totalSize = 5000000;
3    %let DupRate = 0.8;
4
5    data total;
6    array v(*) v1 - v5;
7    do i=1 to &totalSize;
8       do j=1 to dim(v);
9          v(j) = 100 + 2 - rantbl(-1, (1 - &DupRate ));
10      end;
11      output;
12   end;
13   drop i j;
14   run;

NOTE: The data set WORK.TOTAL has 5000000 observations and 5 variables.
NOTE: DATA statement used:
      real time           16.16 seconds
      user cpu time       13.48 seconds
      system cpu time     0.70 seconds
      Memory                            96k


15
16   proc sql;
17   create table out3
18   as
19   select *
20   from total
21   group by v1,v2,v3,v4,v5
22   having count(v1)>1;
NOTE: Table WORK.OUT3 created, with 32 rows and 5 columns.

23   quit;
NOTE: PROCEDURE SQL used:
      real time           5:09.97
      user cpu time       27.63 seconds
      system cpu time     8.31 seconds
      Memory                            2299k


24
25   proc summary data=total nway;
26   class v1 - v5;
27   output out=out2(drop=_type_ where=(_freq_>1));
28   run;

NOTE: There were 5000000 observations read from the data set WORK.TOTAL.
NOTE: The data set WORK.OUT2 has 32 observations and 6 variables.
NOTE: PROCEDURE SUMMARY used:
      real time           13.76 seconds
      user cpu time       7.19 seconds
      system cpu time     0.39 seconds
      Memory                            129k[/code:72d04]
IBM ThinkPad T23
PIII 1.13G 512M SDRAM

shiyiming

[quote="shiyiming":8b942]我的结果和结论与SAS_Dream非常相似，机器刚重起，没有其他作业。
我把SQL和SUMMARY的位置对调了，结果还是一样。
运行完程序后,现在机器变得暴慢！
[/quote:8b942]

<!-- s:wink: --><img src="{SMILIES_PATH}/icon_wink.gif" alt=":wink:" title="Wink" /><!-- s:wink: --> 估计SQL用完了内存也死抱着不还，"400M是我的"
Sharwyn他家富，好几个G的内存，不还就不还，像你我这样，512M就敢出来混，不暴慢就怪了。

另外，老大可以调一调config里的sortsize, 调到和你的物理内存一样，这时你可以观察到SQL使用的内存在加大，速度也会不同。

shiyiming

[quote:19a25]1        options fullstimer msglevel=i;
2
3        %let totalSize = 5000000;
4
5        %let DupRate = 0.8;
6
7      data total;
8        array v(*) v1 - v5;
9          do i=1 to &amp;totalSize;
10         do j=1 to dim(v);
11             v(j) = 100 + 2 - rantbl(-1, (1 - &amp;DupRate ));
12         end;
13         output;
14      end;
15      drop i j;
16      run;

NOTE: The data set WORK.TOTAL has 5000000 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           9.73 seconds
      user cpu time       4.43 seconds
      system cpu time     1.06 seconds
      Memory                            80k


17
18      proc summary data=total nway;
19      class v1 - v5;
20      output out=out2(drop=_type_ where=(_freq_&gt;1));
21      run;

NOTE: Multiple concurrent threads will be used to summarize data.
NOTE: There were 5000000 observations read from the data set WORK.TOTAL.
NOTE: The data set WORK.OUT2 has 32 observations and 6 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
      real time           5.03 seconds
      user cpu time       8.56 seconds
      system cpu time     1.14 seconds
      Memory                            94k


22
23      proc sql;
24      create table out3
25      as
26      select *
27      from total
28      group by v1,v2,v3,v4,v5
29      having count(v1)&gt;1;
NOTE: Table WORK.OUT3 created, with 32 rows and 5 columns.

30      quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           18.60 seconds
      user cpu time       18.04 seconds
      system cpu time     2.48 seconds
      Memory                            316k

[/quote:19a25]


联想 P4 3.2G 内存2G DDR400 台式电脑

shiyiming

[quote:bca75]1        options fullstimer msglevel=i;
2
3        %let totalSize = 5000000;
4
5        %let DupRate = 0.8;
6
7      data total;
8        array v(*) v1 - v5;
9          do i=1 to &amp;totalSize;
10         do j=1 to dim(v);
11             v(j) = 100 + 2 - rantbl(-1, (1 - &amp;DupRate ));
12         end;
13         output;
14      end;
15      drop i j;
16      run;

NOTE: The data set WORK.TOTAL has 5000000 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           9.73 seconds
      user cpu time       4.43 seconds
      system cpu time     1.06 seconds
      Memory                            80k


17
18      proc summary data=total nway;
19      class v1 - v5;
20      output out=out2(drop=_type_ where=(_freq_&gt;1));
21      run;

NOTE: Multiple concurrent threads will be used to summarize data.
NOTE: There were 5000000 observations read from the data set WORK.TOTAL.
NOTE: The data set WORK.OUT2 has 32 observations and 6 variables.
NOTE: PROCEDURE SUMMARY used (Total process time):
      real time           5.03 seconds
      user cpu time       8.56 seconds
      system cpu time     1.14 seconds
      Memory                            94k


22
23      proc sql;
24      create table out3
25      as
26      select *
27      from total
28      group by v1,v2,v3,v4,v5
29      having count(v1)&gt;1;
NOTE: Table WORK.OUT3 created, with 32 rows and 5 columns.

30      quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           18.60 seconds
      user cpu time       18.04 seconds
      system cpu time     2.48 seconds
      Memory                            316k

[/quote:bca75]


联想 P4 3.2G 内存2G DDR400 台式电脑