Date:         Fri, 4 Aug 2006 13:20:36 -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: Match merge question

On Fri, 4 Aug 2006 10:57:07 -0400, Ken Karan <posible88-sswug@YAHOO.COM> wrote:

>I like datanull's suggestion best.  Mine is less elegant but works in a
>brute-force/control-freak kinda way:
>
>data main;
>    length addr $ 13;
>    input id $ trm addr $;
>    list;
>    cards;
>1 1 333mainstreet
>1 2 333mainstreet
>1 3 333mainstreet
>2 1 111mainstreet
>;
>    run;
>
>data upd;
>    length addr $ 13;
>    input id $ addr $;
>    cards;
>1 001abcstreet
>;
>    run;
>
>data main (drop=AddrM AddrU);
>    merge
>        main (in=a rename=(addr=AddrM))
>        upd  (in=u rename=(addr=AddrU))
>        ;
>    by id;
>    if a then do;
>        if u then Addr = AddrU;
>        else Addr = AddrM;
>        output;
>        end;
>    run;
>
>proc print data=main;

I think Ken's solution is the best immediate fix to Susie's problem. The
renaming eliminates the collision of like-named non-BY variables.

Note that Susie's MAIN table has BY groups with multiple observations. In a
MERGE, variable collisions can cause trouble, BY groups with multiple
observations can cause trouble, and the interaction of these two
circumstances is pretty likely to cause trouble.

If Susie were prepared to contemplate some design changes, a better solution
might emerge. I would suggest maintaining the addresses (and perhaps other
one-per-ID variables) in a satellite table with non-repeating ID values.
Then she could readily use UPDATE or MODIFY to make corrections, as somebody
else suggested.

On 8/3/06, Susie Li <Susie.Li@tvguide.com> wrote:
> I thought I would be able to update ALL ADDRESS data in dataset MAIN
> with the new address in dataset UPD.  But the following match-merge on
> ID only updated the 1st record in MAIN.  What is wrong?
>
> ----
>
> data main;
>     input id $ trm addr $;
>     cards;
> 1 1 333mainstreet
> 1 2 333mainstreet
> 1 3 333mainstreet
> ;
>
> data upd;
>     input id $ addr $;
>     cards;
> 1 001abcstreet
> ;
>
> data main;
>     merge main (in=a) upd;
>     by id;
>     if a;
> run;
>
>
>
> Susie Li
> TV Guide
> 1211 Avenue of the Americas
> New York, NY 10036
> Tel 212.852.7453
> Email susie.li@tvguide.com