Date:         Fri, 17 Jan 1997 10:09:00 -0500
Reply-To:     Dave_Mabey_at_RDA8POSTOFFICE1%RDCCMAIL%READERSDIGEST@RMAIL.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Dave_Mabey_at_RDA8POSTOFFICE1%RDCCMAIL%READERSDIGEST@RMAIL.COM
Subject:      Re: MERGE or UPDATE
Comments: To: pbook@PGH.NET
Content-type: text/plain; charset=US-ASCII

New Text Item:  MERGE or UPDATE
 
 
   The following works for me.
 
   data file1;
     input color $ item $ price dollar10.;
     cards;
   red   sweater  $10
   blue  pants     $100
   green shirt     $20
   red sweater    $15
   red sweater    $25
   blue sweater   $3
   purple shirt    $5
   white sweater $6
   ;;;;
 
   data file2;
     input color $ item $ ;
     cards;
    red sweater
    black pants
   ;;;;
 
   proc sql;
      delete
       from file1
         where item||color
           in (select item||color
               from file2);
 
 
 
 
______________________________ Reply Separator ____________________________
_____
Subject: MERGE or UPDATE
Author:  pbook@PGH.NET
Date:    1/14/97 4:05 PM
 
 
(Embedded image moved to file: PIC001.PCX)
I would like to do the following and can only think of it in a SAS DB2 way.
I would like to use one file as a master file and I would like to delete
every like occurence from the master file according to the contents of the
second file.
(by the way file1 and file2 are actually db2 tables...if it matters)
 
file1
red   sweater  $ 10
blue  pants     $100
green shirt     $  20
red sweater    $  15
red sweater    $  25
blue sweater   $   3
purple shirt    $  5
white sweater $  6
 
file 2
red sweater
 
 
data final;
Merge file1 file2;
by color item;
(if color and item in file1 = color and item in file2 then delete)
 
want to get rid of all red sweaters in Christmas inventory.
 
SAS manuals are not helping at this point.  I have proc printed output of
every step and cannot get the desired result.
 
Thanks in advance.
SMTPOriginator: owner-sas-l@VM.MARIST.EDU