topic Re: merge statement in SAS Programming

merge statement

HeatherNewton — Fri, 04 Mar 2022 07:09:15 GMT

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?

Re: merge statement

Astounding — Fri, 04 Mar 2022 07:25:05 GMT

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)

Re: merge statement

HeatherNewton — Fri, 04 Mar 2022 08:13:36 GMT

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

Re: merge statement

Kurt_Bremser — Fri, 04 Mar 2022 11:15:38 GMT

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.

Re: merge statement

Cynthia_sas — Fri, 04 Mar 2022 13:53:23 GMT

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