如何根据bbb数据集中变量visit的取值补全数据集aaa

1. data aaa;
   
2. input id visit x;
   
3. cards;
   
4. 1 1 2
   
5. 1 2 1
   
6. 1 3 3
   
7. 2 1 2
   
8. 2 2 3
   
9. 3 1 1
   
10. ;
    
11. run;
    
12. 
    
13. data bbb;
    
14. input visit;
    
15. cards;
    
16. 1
    
17. 2
    
18. 3
    
19. ;
    
20. run;

复制代码

如何根据bbb数据集中变量visit的取值补全数据集aaa，希望得到的数据集为：id visit x
1 1 2
1 2 1
1 3 3
2 1 2
2 2 3
2 3 .
3 1 1
3 2 .
3 3 .
谢谢！

proc sql;
create table wanted as
select b.*, a.x
from
(select *
from (select distinct id from aaa), (select distinct visit from bbb)) b
left join aaa a
on a.id=b.id and a.visit=b.visit;
quit;

wwang111 发表于 2019-5-26 17:07
proc sql;
create table wanted as
select b.*, a.x

data步可以实现吗？

1. data aaa;
   
2. input id seq$ visit y;
   
3. cards;
   
4. 1 a 1 1
   
5. 1 a 2 2
   
6. 1 b 1 3
   
7. 1 b 2 4
   
8. 1 b 3 5
   
9. 2 a 1 1
   
10. 2 b 1 3
    
11. 2 b 2 4
    
12. ;
    
13. run;
    
14. 
    
15. data bbb;
    
16. input visit;
    
17. cards;
    
18. 1
    
19. 2
    
20. 3
    
21. ;
    
22. run;

复制代码

如何得到这样的结果
1 a 1 1
1 a 2 2
1 a 3 .
1 b 1 3
1 b 2 4
1 b 3 5
2 a 1 1
2 a 2 .
2 a 3 .
2 b 1 3
2 b 2 4
2 b 3 .

1. data bbb_;
   
2.         set bbb;
   
3.         do id=1 to 2;
   
4.                 seq='a';
   
5.                 output;
   
6.                 seq='b';
   
7.                 output;
   
8.         end;
   
9. run;
   
10. 
    
11. proc sort data=aaa; by id seq visit; run;
    
12. 
    
13. proc sort data=bbb_; by id seq visit; run;
    
14. 
    
15. data ccc;
    
16.         merge aaa bbb_;
    
17.         by id seq visit;
    
18. run;

复制代码