Date:         Thu, 8 Feb 2001 17:11:40 -0700
Reply-To:     Kenneth Moody <KennethMoody@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Kenneth Moody <KennethMoody@FIRSTHEALTH.COM>
Subject:      Re: Behaviour of many-many match merging in SAS
Content-Type: text/plain; charset=us-ascii

As noted below, Howard observes that the behavior of a many to many MERGE differs depending on whether there is a BY statement.

TS-644, which has been mentioned earlier in this thread, states on page 1 - "the key concept to understanding how MERGE works is to realize that 'the BY statement modifies the action of the SET, MERGE, or UPDATE statement by controlling when the values in the program data vector are set to missing.  During BY-group processing, the SAS System retains the values of variables until it has copied the last observation it finds for that BY group in any of the data sets.' (SAS Language Reference, Version 6, First Edition p 135 or SAS Language Reference, Concepts: Version 8 p309)"

So it appears that the different behavior with a BY statement is a documented feature of the system.

Ken Moody
First Health, Metrics Department
Voice: 916-374-3924
EMail: KennethMoody@firsthealth.com

>>> "Doug Dame" <dameda@SHANDS.UFL.EDU> 02/08 10:46 AM >>>
Howard Schreier <Howard_Schreier@ITA.DOC.GOV> is bothered that

>>  the behavior of a many-to-many MERGE within a BY group
>>  is not the same as the behavior of a many-to-many MERGE
>>  in the absence of a BY statement.

I'm snipping his example entirely (mostly because I would have had to include the whole thing), but the end result is

MERGE w/ no by-var

>>   Obs    left    right
>>      1      A        1
>>      2               2

And MERGE with a (subsequently dropped) by-var

>>   Obs    left    right
>>     1      A        1
>>     2      A        2

And concludes:

>>  It seems to me that the two outputs should be the same,
>>  one way or the other. But they are not. If this is consistent
>>  behavior, it is consistent in some rather subtle way.


I think it all makes sense if you assume a "no by-var" merge is in fact merely short-hand for:

   data COMBINE;
   merge LEFT RIGHT;
   by implicit_by-var [valued as _N_]

In that case, each record would be processed as a FIRSTdot. Hence the variable LEFT is re-initialized as 'blank' at the top of the PDV when Mr. SAS recognizes that there is no incoming record in the LEFT dataset that has a by-value of '2'.


HTH

Douglas Dame
Shands HealthCare
Gainesville FL USA