DOCUMENTATION
Sign In

Get access to My SAS, trials, communities and more.

Working with Flows
 

Compare Data

About the Compare Data Step

The Compare Data step compares two data sets or compares two variables within or across data sets.

Note: This step is available to use in a flow only if your site licenses SAS Data and AI Studio Advanced or SAS Data and AI Studio Enterprise . You can use this step as a standalone step in SAS Data and AI Studio Basic, SAS Data and AI Studio Advanced, or SAS Data and AI Studio Enterprise . For more information, see Summary of Flow Functionality in SAS Data and AI Studio: User’s Guide.

Compare Data: Connection Requirements for the Node

In order to run the Compare Data step, you must create connections to the input node.

Input Port

Output Port

If you are comparing variables within the same table, you need only one input table. If you are comparing across tables, you need a base data set and a comparison table.

  • Table node

or

  • Operational node (for example, a Query node, a SAS Program node, or an Import node) that creates an output table

No connection is required.

Note: By default, the output data is written to temporary tables in the Work library. You can specify the library and name of the output tables by connecting the output ports to Table nodes. For more information, see Adding a Table from a SAS Library to a Flow.

Compare Data: Two Data Sets

Step 1: Select the Input Data Sources

  1. Add the base table and comparison table to your flow. Connect the input ports of the Compare Data node to the data sources. For more information, see Connecting Nodes.
  2. Select the Compare Data node in the flow.
  3. On the Select Tables tab, select Between 2 data sets from the Compare data drop-down list.

Step 2: Select the Columns to Compare

On the Select Columns tab, the options that are available depend on whether you are comparing the data by observations or by ID variables.

To compare the data by observations:

  1. From the Match data by drop-down list, select Observations.
  2. Specify whether to compare all the columns in the data sets or to select the columns to compare. Click Select columns to select the columns from each table to compare.

    By default, the Compare Data step adds a column with the same name for comparison. To change the name of a column, click Select column.

To compare the data by ID variables:

  1. From the Match data by drop-down list, select ID variables.
  2. In the navigation pane, click ID variables. Click Select columns to select the columns from each table that contain the ID variables.
  3. In the navigation pane, click Compare columns. Specify whether to compare all the columns in the data sets or to select the columns to compare. Click Select columns to select the columns from each table to compare.

    By default, the Compare Data step adds a column with the same name for comparison. To change the name of a column, click Select column.

Step 3: Select the Comparison Criteria

On the Comparison Criteria tab, you can set these options.

  1. Select the method for judging equality. Numeric values are determined to be unequal if the magnitude of their difference, as determined by the method that you select, is greater than the value of the equality criterion.

    The following methods are available:

    • Absolute - compares the absolute difference of the values to the value of the equality criterion. If the absolute value of y minus x is greater than the equality criterion, then the values are determined to be unequal.
    • Exact - tests for exact equality. If the value of y does not equal the value of x, then the values are determined to be unequal.
    • Percent - compares the absolute percent difference to the value of the equality criterion.
    • Relative - compares the absolute relative difference to the value of the equality criterion.
    Note: For the Absolute, Percent, and Relative methods, the default value is 0.00001. You can specify the value for the equality criterion.
  2. Specify how to treat missing values. You can choose from these options: Treat a missing value in the BASE data set as equal to any value or Treat a missing value in the COMPARISON data set as equal to any value.

Step 4: Create an Output Data Set

On the Output Data tab, you can set these options.

  1. Select Include output data set to create an output data set that contains a row for each matching observation. This data set contains a column for each variable in the observation, a column for the type of observation (_TYPE_), and a column for observation number (_OBS_). The values in the _TYPE_ column can be one of these types:
    • BASE - The values in this observation are from an observation in the base data set.
    • COMPARE - The values in this observation are from an observation in the comparison data set.
    • DIF - The values in this observation are the differences between the values in the base and comparison data sets.
    • PERCENT - The values in this observation are the percent differences between the values in the base and comparison data sets.

    You can choose to include these observations in the output data set:

    • Write an observation for each observation in the base data writes in the output data set the observations in the base data set. The value in the _TYPE_ column of the output data set is set to BASE.
    • Write an observation for each observation in the comparison data writes in the output data set the observations in the comparison data set. The value in the _TYPE_ column of the output data set is set to COMP.
    • Include difference value writes in the output data set the differences between the values in the base and comparison data sets. The value in the _TYPE_ column of the output data set is set to DIF.
    • Include values for the percent differences writes in the output data set the percent differences between the values in the base and comparison data sets. The value in the _TYPE_ column of the output data set is set to PERCENT.
    • Suppress observations when all values are equal does not include in the output data set any observations where all of the values of the variables are equal.
  2. Select Include output data set for summary statistics to create an output data set that contains a row for each summary statistic for each pair of variables. The output data set contains these columns:
    • _VAR_ - contains the name of the variable from the base data set.
    • _WITH_ - contains the name of the variable from the comparison data set.
    • _TYPE_ - contains the name of the statistic in the observation.
    • _BASE_ - contains the value of the statistic that is calculated from the values of the variable in the base data set with matching observations in the comparison data set.
    • _COMP_ - contains the value of the statistic that is calculated from the values of the variable in the comparison data set with matching observations in the base data set.
    • _DIF_ - contains the value of the statistic that is calculated from the differences of the values of the variable in the base data set and the matching variable in the comparison data set.
    • _PCTDIF_ - contains the value of the statistic that is calculated from the percent differences of the values of the variable in the base data set and the matching variable in the comparison data set.
  3. Specify the maximum number of differences to print.

Compare Data: Comparing Variables within the Same Data Set

Step 1: Select the Input Data Source

  1. Add the input table to the flow. Connect the input port of the Compare Data node to the data source. For more information, see Connecting Nodes.
  2. Select the Compare Data node in the flow.
  3. On the Select Tables tab, select Within a single data set from the Compare data drop-down list.

Step 2: Select the Columns to Compare

On the Select Columns tab, select the columns from the input table to compare.

To add a variable pair to the table, click Select Columns.

By default, the Compare Data step adds a column with the same name for comparison. To change the name of a column, click Select column.

Step 3: Select the Comparison Criteria

On the Comparison Criteria tab, you can set these options.

  1. Select the method for judging equality. Numeric values are determined to be unequal if the magnitude of their difference, as determined by the method that you select, is greater than the value of the equality criterion.

    The following methods are available:

    • Absolute - compares the absolute difference of the values to the value of the equality criterion. If the absolute value of y minus x is greater than the equality criterion, then the values are determined to be unequal.
    • Exact - tests for exact equality. If the value of y does not equal the value of x, then the values are determined to be unequal.
    • Percent - compares the absolute percent difference to the value of the equality criterion.
    • Relative - compares the absolute relative difference to the value of the equality criterion.
    Note: For the Absolute, Percent, and Relative methods, the default value is 0.00001. You can specify the value for the equality criterion.
  2. Specify how to treat missing values. You can choose from these options: Treat a missing value in the BASE data set as equal to any value or Treat a missing value in the COMPARISON data set as equal to any value.

Step 4: Create an Output Data Set

On the Output Data tab, you can set these options.

  1. Select Include output data set to create an output data set that contains a row for each matching observation. This data set contains a column for each variable in the observation, a column for the type of observation (_TYPE_), and a column for observation number (_OBS_). The values in the _TYPE_ column can be one of these types:
    • BASE - The values in this observation are from an observation in the base data set.
    • COMPARE - The values in this observation are from an observation in the comparison data set.
    • DIF - The values in this observation are the differences between the values in the base and comparison data sets.
    • PERCENT - The values in this observation are the percent differences between the values in the base and comparison data sets.

    You can choose to include these observations in the output data set:

    • Write an observation for each observation in the base data writes in the output data set the observations in the base data set. The value in the _TYPE_ column of the output data set is set to BASE.
    • Write an observation for each observation in the comparison data writes in the output data set the observations in the comparison data set. The value in the _TYPE_ column of the output data set is set to COMP.
    • Include difference value writes in the output data set the differences between the values in the base and comparison data sets. The value in the _TYPE_ column of the output data set is set to DIF.
    • Include values for the percent differences writes in the output data set the percent differences between the values in the base and comparison data sets. The value in the _TYPE_ column of the output data set is set to PERCENT.
    • Suppress observations when all values are equal does not include in the output data set any observations where all of the values of the variables are equal.
  2. Select Include output data set for summary statistics to create an output data set that contains a row for each summary statistic for each pair of variables. The output data set contains these columns:
    • _VAR_ - contains the name of the variable from the base data set.
    • _WITH_ - contains the name of the variable from the comparison data set.
    • _TYPE_ - contains the name of the statistic in the observation.
    • _BASE_ - contains the value of the statistic that is calculated from the values of the variable in the base data set with matching observations in the comparison data set.
    • _COMP_ - contains the value of the statistic that is calculated from the values of the variable in the comparison data set with matching observations in the base data set.
    • _DIF_ - contains the value of the statistic that is calculated from the differences of the values of the variable in the base data set and the matching variable in the comparison data set.
    • _PCTDIF_ - contains the value of the statistic that is calculated from the percent differences of the values of the variable in the base data set and the matching variable in the comparison data set.
  3. Specify the maximum number of differences to print.
Last updated: September 16, 2026