merge statement

[HeatherNewton]

data hello;
     merge apple (in=1) orange (in=b);
     if weight~=1,
     by fruit_id;
     if a and b; 
     FORMAT size 10.4 price 5.4;
     if weight>20 OR price>30 then do:
     margin=20;
     end;
run;

my questions what does if a and b means here and is its significance to the action of merging? does the merging only happen when the few lines of lf then else statement is true?

[Astounding]

Because of errors you made when copying the program, it's anybody's guess what the program will do. Fix the mistakes so we can explain the correct program. At a minimum, move the BY statement so it immediately follows the MERGE statement. And change (in=1) to (in=a)

[HeatherNewton]

data hello;
     merge apple (in=a) orange (in=b);
     if weight~=1;
     by fruit_id;
     if a and b; 
     FORMAT size 10.4 price 5.4;
     if weight>20 OR price>30 then do;
     margin=20;
     end;
run;

fixed! thx

[Kurt_Bremser]

if a and b;

is a so-called SUBSETTING IF that evaluates the variables a and b. These variables are defined in your MERGE statement and indicate which dataset(s) contributed data to the current iteration of the DATA step by having values of either 0 (false) or 1 (true).

So all processing after this statement will only occur during any given iteration of the data step when both datasets have at least one observation with a matching fruit_id.

The actual result of each iteration depends on the location of the weight and price variables, and the respective number of observations in the datasets for a given fruit_id.

[Cynthia_sas]

Hi:

  To help you get a handle on using the MERGE statement with the IN= option, consider these examples.

Common merge example with 2 datasets:

All possible output tables from merging 2 datasets:

Code to make data and do merge:

data mtable1;
  infile datalines;
  input commonvar amount;
  return;
  datalines;
11 115
13 315
14 415
16 615
;
run;

data mtable2;
  infile datalines;
  input commonvar name $ zip $;
return;
datalines;
10 Alicia 12345
11 Bryce 23456
12 Carolyn 34567
13 David 45678
14 Elsa 56789
15 Fern 67890
;
run;

** merge with all possible outputs;
data both oneonly twoonly allofone alloftwo allobs;
   merge mtable1(in=inone) mtable2(in=intwo);
   by commonvar;
   if inone then do;
      output allofone;
      if inone and intwo then output both;
      else if inone and not intwo then output oneonly;
   end;
   if intwo then do;
      output alloftwo;
      if intwo and not inone then output twoonly;
   end;
   if inone or intwo then output allobs;
run;

Results:

  We cover the MERGE statement in detail in our Programming 2 course.

Cynthia