Merge with OR

[David_Billa]

Merge with OR equals full join or cartesian product in the step below?

data FORECASTS;
    merge SOP(in=SOP)
          CF(in=CF)
          CLF(in=CLF)
          STAT(in=STAT);
    by BUSINESS_UNIT SALESORG CHANNEL DP_MATERIAL EXPLODED_MATERIAL FLAG PLANTO LOCATION WEEK ;
    if SOP or CF or CLF or STAT;
  run;

[SASKiwi]

It's most equivalent to an SQL FULL JOIN but can only handle one-to-many BY variable values whereas SQL can do many-to-many variable values. There is no need for your IF statement in your example as that is the default condition.

[Tom]

If you are  not attempting any MANY to MANY matching then it is like an FULL JOIN.  

Note that the IF statement is doing nothing. Every observation must have come from at least one of those four input datasets as they are the only ones on the MERGE statement.

But if there are combinations of those BY variables that have multiple observations in more than one of the inputs then it is not at all like what SQL would consider any type of JOIN.   When you MERGE the observations are matched in the order they appear in the source datasets.  So if there are three observations in SOP and two in CF for some combination of the BY variables then the first two are matched as one observation, then the next two form the second observation.  And for the third observation the values of the variables from SOP are read from the third observation, but for the variables that come from CF the values of variables the value they had on the last observation will be what it output.

[David_Billa]

@Tom Were you able to point me  to any documents which has example for one to many and many to many join via Merge using OR?

[SASKiwi · Accepted solution]

@David_Billa - The MERGE doc has some good examples you can copy and run for yourself. There are one-to-one and many-to-many examples in this link and it would be easy to create a one-to-many from a many-to-many example. Add an IF statement using (IN = ) with OR conditions and examine the output data. That should show you how MERGE works.

[ballardw]

Data step merge behaves quite a bit differently than SQL joins for common-named variables that appear in more than one data set that are NOT on the BY statement. By default only one value is kept and which one may vary depending on the match(s) that occur of the BY variables. Something to always consider with Merge if you are used to thinking in terms of SQL joins.