Overview

In some cases, you might want to create a new table that contains only duplicate rows from a source table. This sample explains how you can use the Query Builder in SAS Enterprise Guide 4.2 to produce this output.

About the input data

The following display shows the input data used in this sample. To get a copy of this data, click the Downloads tab.

Create the table with duplicate rows

1. Open the Query Builder. For example, you can click the Query Builder button above the input table data grid.



2. In the Query Builder window, select the t1 (MYCLASS) table on the available columns side of the Query Builder and drag it onto the Select Data tab.



3. Above the available columns, click the Computed Columns button.
4. In the Computed Columns window, select New to begin creating a new computed column.
5. In the wizard, select Advanced Expression as the type of computed column, and then click Next.
6. In the expression builder, expand the Functions list and scroll down the list until you see COUNT * Aggregate. Double-click to add that function to the expression above. Click Next.



7. Change the column name and alias to CT. Click Finish, and then click Close to add the CT column to your selected data of your query.
8. To add the group variables that you are using to look for duplicate rows, uncheck the Automatically select groups check box, which appears at the bottom of the window in the Summary groups section, .
9. Select the Edit Groups button to select the grouping variables. In the Edit Groups window, select Name, Sex, and Age under the Select Data Columns heading in the Available Columns section, and click the right arrow to move them to the Group By section. Click OK to close the window.
10. Select the Filter Data tab. Drag the CT column from the available columns on the left to the Filter the summarize data section. After you drop the column, the New Filter wizard will open.
11. Change the Operator to Greater than, and type the number 1 into the Value field. Click Finish to close the filter wizard.



12. (Optional) The CT computed column was automatically added to the Select Data tab after you created it. You needed to keep this variable in the selected data in order to create the grouping and filters in the previous step. However, now that the grouping and filter are done, you can remove it from the Select data tab if you do not want to have this count variable in your output table. To do this, select the CT variable in the selected data list, and click to delete it. The computed column still remains in your available columns list.

Reviewing the results

At this point, the query has now been defined and should look similar to this display.

Select Run to run the query. The output table should look similar to the following display.

Additional Documentation

For more information about using SAS Enterprise Guide, see the SAS Enterprise Guide documentation page.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Myclass.sas7bdat

This sample will show you how to use the query builder in SAS Enterprise Guide to create a table that contains only the duplicate rows from another table.

Type:	Sample
Topic:	Common Programming Tasks ==> Selecting Data

Date Modified:	2010-07-20 15:03:01
Date Created:	2010-07-14 12:24:38

Operating System and Release Information

Product Family	Product	Host	Product Release		SAS Release
			Starting	Ending	Starting	Ending
SAS System	SAS Enterprise Guide	Microsoft® Windows® for x64	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Datacenter Edition	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Enterprise Edition	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Standard Edition	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 for x64	4.2		9.2 TS2M0
		Microsoft Windows Server 2008 for x64	4.2		9.2 TS2M0
		Microsoft Windows XP Professional	4.2		9.2 TS2M0
		Windows Vista	4.2		9.2 TS2M0
		Windows Vista for x64	4.2		9.2 TS2M0