问一个根据多个表生成数据的问题

shiyiming

假设我有以下三个表account，a，b，其中account是总表，a和b是两个分表，a和b中有些ID可能是重复的。
我现在需要再表account加上两个变量var1，var2，如果ID来自于表a，那么var1 =1,否则var1 =0；同样，如果ID来自于表b，那么var2 =1,否则var2 =0。
谢谢高手~

data account;
input ID;
cards;
001
002
003
004
005
006
007
008
009
;

data a;
input ID;
cards;
001
002
003
007
008
009
;

data b;
input ID;
cards;
001
002
003
004
005
006
;

shiyiming

[code:28kdf9cv]data a;
    input ID;
cards;
001
002
003
007
008
009
;
data b;
    input ID;
cards;
001
001
002
003
004
005
006
;
data account;
    input ID;
cards;
001
002
003
004
005
006
007
008
009
;
data out;
    if _n_=1 then do;
        declare hash a(hashexp:16,dataset:'a',ordered:'yes');
        a.defineKey('id');
        a.defineDone();
        declare hash b(hashexp:16,dataset:'b',ordered:'yes');
        b.defineKey('id');
        b.defineDone();
    end;
    set account;
    var1=ifn(a.find(),0,1);
    var2=ifn(b.find(),0,1);
run;[/code:28kdf9cv]

shiyiming

sql也可以实现吧。

shiyiming

data account;
input ID;
cards;
001
002
003
004
005
006
007
008
009
;

data a;
input ID;
cards;
001
002
003
007
008
009
;

data b;
input ID;
cards;
001
002
003
004
005
006
;
proc sql;
create table result as
select id,1 as var1 from account where id in (select id from a )
union
select id ,0 as var1 from account where id not in (select id from a);
create table result as
select id ,var1,1 as var2 from result where id  in (select id from b)
union
select id ,var1 ,0 as var2 from result where id not in (select id from b);
quit;
proc print;
run;

shiyiming

hopewell确实写的好
SQL实现我写了一个
不过感觉还可以优化一下

shiyiming

两位都是高手，谢谢~ <!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->

shiyiming

to ganshenme
like SASsy way to code this:

proc sql;
       create view accountv as
       select * from account order by id;
       create view av as
       select * from a order by id;
       create view bv as
       select * from b order by id;
quit;
data new;
       merge accountv(in=_1)  av(in=_a)  bv(in=_b);
       by ID;
       var1=(_a=1);  var2=(_b=1);
       if _1;
run;

shiyiming

[code:2z03jzxr]
/*STEP1&#58; CREATE A FORMAT TO INDICATE Y/N*/
proc format;
        value define            &#46; = 0
                                          other =  1
        ;
run;
/*STEP2&#58; LEFT JOIN THE THREE TABLES*/
proc sql;
        select  account&#46;id, a&#46;id as var1 format=define&#46;, b&#46;id as var2 format=define&#46;
        from  account  left join a on account&#46;id = a&#46;id
                              left join b on  account&#46;id = b&#46;id
        ;
quit;
********************TEST PASSED***********************;       
[/code:2z03jzxr]

shiyiming

to oloolo
原来data step也能merge三个dataset啊，受教了。

shiyiming

小弟也试着写了一下上面的程序，请高手指正哈~-~
[code:r878dgef]data account;
input ID;
cards;
001
002
003
004
005
006
007
008
009
;
data a;
input ID;
cards;
001
002
003
007
008
009
;
data b;
input ID;
cards;
001
002
003
004
005
006
;
proc sql;
   create table  result as
   select id,id in(select id from a) as var1,id in(select id from b) as var2
   from account;
quit;[/code:r878dgef]