如何对两个数据集进行匹配

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

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

立即领取

感谢您参与论坛问题回答

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

+2 论坛币

有两个数据集A和B，如下：

数据集A				数据集B
date	group	no		date	group	no
2012-1-3	a	2		2012-1-8	a	4
2012-1-11	a	4		2012-1-13	a	8
2012-1-12	a	5		2012-1-18	a	4
2012-1-6	b	3		2012-2-5	b	3
2012-1-21	b	7		2012-3-8	c	2
2012-2-8	c	11		2012-3-17	c	10
2012-3-9	c	45		2012-1-17	d	4
2012-5-10	c	2
2012-1-11	d	113

想得到的结果是针对B中每一条数据都要找到A中的一条数据相对应，并将B的"date"、"NO"连接到A数据集中，规则：A中满足”date"<=B中"date" 且 A 中“group"=B中“group"条件的所有数据中“NO”最大的数据，从B的第一条开始匹配，一旦匹配成功那么A中该条数据就不参与下一次匹配过程，有点类似不放回抽样。请各位大牛予以帮助，拜谢。

扫码加我 拉你入群

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

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

分享0 收藏2 回帖

关键词：数据集 Group date ATE ODA 如何

match by which column?

assume only group is character and no missing. jingju
我想问题有些说反了.我理解是把A里的匹配到B里.

1. data _null_;
   
2. call symputx('_ab', nobs);
   
3. set a nobs =nobs; stop;
   
4. run;
   
5. data ab;
   
6. array t[&_ab,2] _temporary_; array tg[&_ab] $ _temporary_;
   
7. if _n_ =1 then do p =1 to nobs;
   
8. set a point =p nobs =nobs;
   
9. t[p,1]=date; t[p,2] =no; tg[p] =group;
   
10. end;
    
11. set b;
    
12. do i =1 to dim1(t);
    
13. if (group =tg[i]) then if (date >=t[i,1]) then if (t[i,2] >no_a) then do;
    
14. no_a =t[i,2]; date_a =t[i,1]; k =i;
    
15. end;
    
16. end;
    
17. if ^missing(k) then call missing(of tg[k]);
    
18. drop i k;
    
19. run;

复制代码

.....

如果GROUP 下面还有SUBGROUP(也是字符型，比如a1-a3)应当如何处理呢？如果GROUP包含缺失值会导致什么错误呢？请指教，谢谢!

nuomaniya 发表于 2013-1-17 15:39
如果GROUP 下面还有SUBGROUP(也是字符型，比如a1-a3)应当如何处理呢？如果GROUP包含缺失值会导致什么错误呢 ...

add one more condition for the subgroup as we did for group.
Missing group values in set B are misleading here because we don't know which groups in A should be matched. I would delete the missing group from B before matching or conditionally excute the code for missing goup. For example,

1. if ^missing(group) then do;
   
2. ...
   
3. end;

复制代码

The consequence of including missing groups in our code is, since we are sampling without replacement, we reset the matched group in A as missing after one match in order to prevent from next match and thus the reset missing could be mixed with the original missing.
Jingju

谢谢大牛了，还有个问题请教，如果在A和B中都增加一列“NEW_NO”（相同GROUP中不重复）, 匹配条件变成：A中满足”date"<=B中"date" 且 A 中“group"=B中“group"条件的所有数据中“NO”最大的数据，如果"NO“相同，取"NEW_NO”最小的，应当如何处理呢？

请大牛们多多指教，谢谢

nuomaniya 发表于 2013-1-18 23:42
请大牛们多多指教，谢谢

1. data _null_;
   
2. call symputx('_ab', nobs);
   
3. set a nobs =nobs end =Eof;
   
4. retain max_no_new .;
   
5. if max_no_new <=no_new then max_no_new =no_new;
   
6. if Eof then call symputx('no_new_a',max_no_new+1);
   
7. run; %put &max_no_new;
   
8. data ab;
   
9. array t[&_ab,4] _temporary_; array tg[&_ab,2] $ _temporary_;
   
10. if _n_ =1 then do p =1 to nobs;
    
11. set a point =p nobs =nobs;
    
12. t[p,1]=date; t[p,2] =no; t[p,3] =no_new; t[p,4] =_obs;
    
13. tg[p,1] =group; tg[p,2] =subGroup;
    
14. end;
    
15. set b;
    
16. no_new_a =&no_new_a;
    
17. *if group is missing in A then not matching to B;
    
18. if ^missing(group) then do i =1 to dim1(t);
    
19. if (group =tg[i,1] & subGroup =tg[i,2]) then if (date >=t[i,1]) then if (t[i,2] >=no_a) then if (t[i,3] <no_new_a) then do;
    
20. date_a =t[i,1]; no_a =t[i,2]; no_new_a =t[i,3]; _obs_a =t[i,4];
    
21. k =i;
    
22. end;
    
23. end;
    
24. *if find matched in B, reset group in B as missing;
    
25. if ^missing(k) then call missing(of tg[k,1]);
    
26. *if no match was in B then reset as missing;
    
27. if missing(k) then call missing(of no_new_a);
    
28. drop i k no_new;
    
29. run;

复制代码

太感谢了，还想请教 t[p,4] =_obs; _obs_a =t[i,4]; if missing(k) then call missing(of no_new_a);  这几句的用处是什么呢，_obs之前没有定义啊。我发现如果改变了变量名就得不到结果，如下：

1. data a(rename=(date=date1 group=group1 no=no1));
   
2.   input date         group        $ no;
   
3.   datalines;
   
4. 20120103        a        2
   
5. 20120111        a        4
   
6. 20120112        a        5
   
7. 20120106        b        3
   
8. 20120121        b        7
   
9. 20120208        c        11
   
10. 20120309        c        45
    
11. 20120510        c        2
    
12. 20120111        d        113
    
13.     ;
    
14. run;
    
15. 
    
16. data b(rename=(date=date2 group=group2 no=no2));
    
17.   input date         group        $ no;
    
18.   datalines;
    
19. 20120108        a        4
    
20. 20120113        a        8
    
21. 20120118        a        4
    
22. 20120205        b        3
    
23. 20120308        c        2
    
24. 20120317        c        10
    
25. 20120117        d        4
    
26.     ;
    
27. run;
    
28. 
    
29. data _null_;
    
30.         call symputx('_ab', nobs);
    
31.         set a nobs =nobs; stop;
    
32. run;
    
33. data ab;
    
34.         array t[&_ab,2] _temporary_; array tg[&_ab] $ _temporary_;
    
35.         if _n_ =1 then do p =1 to nobs;
    
36.         set a point =p nobs =nobs;
    
37.         t[p,1]=date1; t[p,2] =no1; tg[p] =group1;
    
38.         end;
    
39.         set b;
    
40.         do i =1 to dim1(t);
    
41.         if (group2 =tg[i]) then if (date2 >=t[i,1]) then if (t[i,2] >no1) then do;
    
42.         no1 =t[i,2]; date1 =t[i,1]; k =i;
    
43.         end;
    
44.         end;
    
45.         if ^missing(k) then call missing(of tg[k]);
    
46.         drop i k;
    
47. run;

复制代码