Date:         Thu, 23 Oct 2008 12:39:59 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>"
              <schreier.junk.mail@GMAIL.COM>
Subject:      Re: Table A exclude data in table B
Content-Type: text/plain; charset=ISO-8859-1

On Thu, 23 Oct 2008 00:39:39 -0400, Ya Huang <ya.huang@AMYLIN.COM> wrote:

>Similar, but combine b1,b2 and b3 first with union:
>
>proc sql;
>create table a_butnot_b as
>select name,date
>from a
>except
>select name,date
>from (select name,date from b1 union corr
>      select name,date from b2 union corr
>      select name,date from b3)
>;

You can actually do it with one layer less:

   create table a_butnot_b as
   select name,date
   from a
   except
   (select name,date from b1 union corr
    select name,date from b2 union corr
    select name,date from b3)
   ;

Typically the A table will have some additional satellite columns which are
needed in the result. Add a NATURAL JOIN for that:

   data a;
   set sashelp.class; put _all_;
   run;

   data b1;
   input Name $ Sex $;
   cards;
   Barbara F
   Carol F
   ;

   data b2;
   input Name $ Sex $;
   cards;
   Jane F
   ;

   data b3;
   input Name $ Sex $;
   cards;
   Robert M
   Ronald M
   ;

   proc sql;
   create table a_butnot_b as
   select * from
    ( select name, sex from a
      except
      ( select name, sex from b1 union
        select name, sex from b2 union
        select name, sex from b3
      )
    )
    natural join
    a
   ;
   quit;

>
>
>On Wed, 22 Oct 2008 20:36:14 -0700, tammy1207@YAHOO.COM.TW wrote:
>
>>Thank you. This is exactly what I am looking for.
>>
>>To extend this question, assume I have couple B tables, B1, B2,
>>B3...., which contains the lists that I would like to exclude from
>>table A. How should I conduct it?
>>
>>Tammy
>>
>>
>>
>>On 10?벲?� ?U?ȱ0?ɳ6?? ya.hu...@AMYLIN.COM (Ya Huang) wrote:
>>> proc sql;
>>> create table a_butnot_b as
>>> select name,date
>>> from a
>>> except
>>> select name,date
>>> from b
>>> ;
>>>
>>>
>>>
>>> On Wed, 22 Oct 2008 19:12:02 -0700, tammy1...@YAHOO.COM.TW wrote:
>>> >Hello everyone,
>>>
>>> >In table A, it contains entire data of Name and Date. In table B, it
>>> >lists the Name and Date which are supposed to removed from table A.
>>> >How can I use proc sql or other ways to do the join in order to
>>> >eliminate the list in table B?
>>>
>>> >Sample:
>>>
>>> >Table A
>>>
>>> >Name Date
>>> >Tim     01
>>> >Tim     02
>>> >Tim     03
>>> >Mary   01
>>> >Mary   02
>>> >Mary   03
>>> >Mary   04
>>>
>>> >Table B
>>>
>>> >Name Date
>>> >Tim     03
>>> >Mary   03
>>> >Mary   04
>>>
>>> >Desire final table
>>>
>>> >Name Date
>>> >Tim     01
>>> >Tim     02
>>> >Mary   01
>>> >Mary   02
>>>
>>> >I appriciate any assistance you can lend.
>>>
>>> >Tammy- ?ƒ?Q?ޥΤ妲 -
>>>
>>> - ţ?ܳQ?ޥΤ妲 -