SAS里用sql语句between and实现的匹配功能能否用hash实现？

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

SAS里有两表要合并，表一中有字段a,b；表二中有字段c；两表合并的条件是c的值在a和b之间。我只知道sql的between and语句可以实现，但运行速度很慢，不知道hash能否实现相同功能呢？求指点。。。

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏1 回帖

关键词：Between sql语句 HASH Has Bet between sql语句

depends on you data
if those [a,b] intervals are mutually exclusive, there're ways to fine-tune the join.
on the other hand, if there are many overlaps, it's beyond me.

Try if this code
The results may be slightly different from that of sql, due to the replicate.

1. data a1(keep=ID a b) b1(keep= ID c c1);
   
2.     do Id = 1 to 10;
   
3.            a = int(ranuni(12345)*50);
   
4.            b = int(ranuni(12345)*50);
   
5.            output a1;
   
6.          end;
   
7.     do Id = 1 to 10;
   
8.            c = int(ranuni(12345)*50);
   
9.            c1 = int(ranuni(12345)*50);
   
10.            output b1;
    
11.          end;
    
12. run;
    
13. 
    
14. data ab;
    
15.   if _n_ = 1 then do;
    
16.       set b1;
    
17.       declare hash h(dataset: 'b1');
    
18.        h.definekey('c');
    
19.        h.definedata('c', 'c1');
    
20.        h.definedone();
    
21.      declare hiter iter('h');   
    
22.   end;
    
23.     set a1;
    
24.       rc = iter.first();
    
25.     do while (rc=0);
    
26.       if (a <= c <=b) or (b<=c <= a) then output;
    
27.       rc = iter.next();
    
28.     end;
    
29. run;

复制代码

yongyitian 发表于 2013-10-28 13:02
Try if this code
The results may be slightly different from that of sql, due to the replicate.

谢谢。确实达到了目的，还有一个问题：hash里使用不等式条件是不是比使用等式条件慢？

"If neither contributing dataset is sorted, but one of them does fit into memory, PROC SQL will choose to process the query with a Hash Join (sqxjhsh)" (http://support.sas.com/techsup/technote/ts553.html)
Make sure you have enough memory, and the SQL join will be as efficient as Yongyitian's program.
The option is buffersize (http://support.sas.com/resources ... ings10/139-2010.pdf)
In either method, a Cartisian product is involved, so they should be spending very similar amount of time. It's not a problem as long as you can get your results in a reasonable amount of time.

"If neither contributing dataset is sorted, but one of them does fit into memory, PROC SQL will choose to process the query with a Hash Join (sqxjhsh)" (http://support.sas.com/techsup/technote/ts553.html)
Make sure you have enough memory, and the SQL join will be as efficient as Yongyitian's program.
The option is buffersize (http://support.sas.com/resources ... ings10/139-2010.pdf)
In either method, a Cartisian product is involved, so they should be spending very similar amount of time. It's not a problem as long as you can get your results in a reasonable amount of time.

thank you