As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


SQL UNION operator

From sasCommunity
Jump to: navigation, search


As part of the decommissioning effort for sasCommunity.org this article/tip has been migrated to communities.sas.com.

The new home for this article/tip is SQL UNION operator (https://communities.sas.com/t5/SAS-Communities-Library/SQL-UNION-operator/ta-p/475996)



The UNION operator in Proc SQL is used to append the rows of two or more SELECT statements having the same number of columns with similar data types. Similar columns in each SELECT statement should normally be in the same order, as the Union is done by position not by column name, except where appending dissimilar columns from a select statement is explicitly desired.

Usage notes

Note that, unlike using the SET statement with multiple datasets, the default behavior of the UNION operator is to remove duplicate rows, from the results of each SELECT statement as well as overall, resulting in only DISTINCT rows. If retention of duplicate rows is desired, either OUTER UNION must be specified, or else the ALL operator must be used on individual SELECT statements where duplicates are desired.

Syntax

SELECT column_name(s) FROM table_name1
<OUTER> UNION <ALL>
SELECT column_name(s) FROM table_name2
...