Doing More with Data Management

Combining Data Using a Cartesian Product Match Merge

You can use Combine on the Data Management menu to combine your data in several ways. You can concatenate, interleave, merge, or match merge your data. The Match Merge item enables you to perform a SAS match merge or Cartesian product match merge.

In this section, you use Combine on the Data Management menu to perform a Cartesian product match merge using the AIRLINE.SCHEDULE and AIRLINE.STAFF sample tables. If you have not created the AIRLINE sample tables, see Creating the AIRLINE Sample Tables.

The AIRLINE.STAFF table contains data on each employee, such as first name, last name, and employee number. The AIRLINE.SCHEDULE table includes information about what personnel are assigned to each flight during the first week of March, including flight number, date, and employee number. This Cartesian product matches the first name and last name of each employee to the flights to which they are assigned. The employee and the schedule information are matched using the employee number found in both tables. You create a new table, WORK.STAFSKED, that contains the merged data.

The following displays show listing reports of the AIRLINE.STAFF and AIRLINE.SCHEDULE tables before they are merged.

AIRLINE.STAFF Table

[AIRLINE.STAFF Table]

AIRLINE.SCHEDULE Table

[AIRLINE.SCHEDULE Table]

Additional Information

For additional information about concatenating, interleaving, merging, or match merging your data, refer to the information on the SET and MERGE statements in SAS Language Reference: Dictionary.

Instructions

To display the Match Merge SAS Tables window, follow this selection path starting from the WorkPlace menu:Tasks Data Management Combine Match merge Second MethodThe Match Merge SAS Tables window appears.

Match Merge SAS Tables Window
Use the First table button to select the AIRLINE.SCHEDULE table.
Use the Second table button to select the AIRLINE.STAFF table.
Select Output Table. The Output Table or View window appears. The Output Table or View window is identical to that shown in Output Table or View Window.
Type STAFSKED in the Table/View field as the name of the output table.
Select OK to return to the Match Merge SAS Tables window.

By leaving the defaults of Temporary and Table, a temporary table is created in the WORK library. The WORK.STAFSKED table is deleted when you exit SAS. An indicator next to Temporary shows that this item is selected.
Select Type of column used to match merge the tables. The Select a Merge Method window appears.
Select Combine by columns common to both tables because the employee number column has the same name in both tables.

You combine the columns unique to each table when each table has columns with the same values but different names, for example, if the employee number is named EMPNUM in one table and IDNUM in the other. You combine the columns common to both tables when each table has a column with the same name and the same values, for example, if each table has a column called EMPNUM with the same employee number values.

The Match Merge by Common Columns window appears with a list of columns common to both tables, that is, having the same name and same data type. The only column common to both tables is IDNUM.

Match Merge By Common Columns Window
Select IDNUM, and then select OK to return to the Match Merge SAS Tables window.
Choose Select unique existing columns. The Select Existing columns window appears.

Select Existing Columns Window
Select, in this order, FLIGHT, DATE, FNAME, and LNAME as the columns to be included in the new table WORK.STAFSKED. The order in which you select these columns determines the order in which they appear in the new table.
Select OK to return to the Match Merge SAS Tables window.
Follow this selection path:Run SubmitWhen prompted, select OK, and then select Goback to view the new table. The new table appears in an FSVIEW window.

Match Merge Output
To exit the FSVIEW window, follow this selection path: File Close

Top of Page