Date:         Tue, 24 Oct 2006 17:38:51 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Matching datasets

On Sun, 22 Oct 2006 23:49:29 -0400, souga soga <souga1234@GMAIL.COM> wrote:

>Hi,
>
>I have a dataset
>
>DATA x;
>
>Id="001", RQty = 10;time1="9:30:00"t;OUTPUT; Id="001", RQty =
>20;time1="9:30:00"t;OUTPUT;
> Id="001", RQty = 30;time1="9:30:00"t;OUTPUT;
>
> Id="002", RQty = 10;time1="9:31:00"t;OUTPUT;
> Id="002", RQty = 20;time1="9:31:00"t;OUTPUT;
>RUN;
>
>DATA y;
>Id="001" ,Qty=10;time1="9:30:00"t ;output;
>
>Id="002" ,Qty=5;time1="9:31:00"t ;output;
>RUN;
>
>Match x and y on ID and time1 if any Rqty=Qty within matching ID and time1
> then
>output only that observation.(e.g:"001")
>
>else If no Rqty = Qty then add Rqty and output the observation.(e.g."002")
>
>
>OUTPUT;
>Id="001", RQty = 10;Qty=10;time1="9:30:00"t;
>Id="002";RQty=30 Qty=5 time1="9:31:00"t ;
>
>
>
>Thanks,
>Sa

Data:

   data x;
   input ID RQty;
   cards;
   1 10
   1 30
   2 10
   2 20
   ;

   data y;
   input ID Qty;
   cards;
   1 10
   2  5
   ;

I've left out the time. To include it just extend the BY statement below and
change the "LAST." variable as well.

Here's the merge step:

   data out(drop = match sumRQty);
   do until (last.id);
      merge x y;
      by id;
      sumRQty = sum(sumRQty, rqty);
      if qty=rqty then do;
         output;
         match = 1;
         end;
      end;
   if not match then do;
      rqty = sumRQty;
      output;
      end;
   run;

It can be done without the DO UNTIL loop, but the housekeeping is a bit more
involved.