Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

Proc SQL Merge

Merging data sets can sometimes be easier using an SQL join, particularly when you have more than two tables to merge.

The following is an example of using a full join to combine each row from the first table with every row from the second table (known as the Cartesian product), where the value of the field called NAME is present in both tables. The COALESCE function is used to return the value of NAME from the first table where it is not missing, so if NAME is missing from table A then its value is taken from table B.

data first;
      input name $ sex $;
      datalines;
Ann     F
Steve   M
Mike    M
Mark    M
Linda   F
Sarah   F
run;

data second;
      input name $ hair $;
      datalines;
Ann     Blonde
Ann     Red
Steve   Black
Mike    Brown
Lynne   Brown
run;

proc sql;
  select coalesce(a.name,b.name), sex, hair from
    first a
    full join
    second b
    on a.name=b.name;

quit;

produces the following output:

          sex        hair

Ann       F         Blonde
Ann       F         Red
Linda     F
Lynne               Brown
Mark      M
Mike      M         Brown
Sarah     F
Steve     M         Black