Combining Data with UNION and UNION ALL in PROC SQL

Introduction

In SAS, data combination is a crucial step when you need to merge results from multiple datasets into a unified view for analysis or reporting. PROC SQL provides two key SQL operators for combining data from different tables: UNION and UNION ALL. These operators are essential for data analysts and SAS professionals to understand as they provide different approaches to merging datasets based on distinct or all values in the columns.

In this article, we will dive into the functionality of UNION and UNION ALL in PROC SQL, exploring their differences, use cases, and best practices. We’ll also discuss how to effectively use them for data combination and help you understand when each operator is most appropriate in your SAS workflows.

1. What is UNION in PROC SQL?

The UNION operator in SQL is used to combine the result sets of two or more SELECT queries. When you use UNION, it automatically removes duplicate rows from the final result set. This means that only unique rows will be included, ensuring that each record appears only once, even if it is present in both SELECT statements.

Syntax of UNION:

proc sql;
    select column1, column2
    from table1
    union
    select column1, column2
    from table2;
quit;

In this example, UNION will combine data from table1 and table2, eliminating any duplicate rows from the result set.

Example: Using UNION to Combine Customer Data

Suppose you have two tables: customers_2023 and customers_2024, which contain customer data for two different years. You want to combine these tables and remove duplicate customer entries (e.g., customers who appear in both years).

proc sql;
    select customer_id, customer_name
    from customers_2023
    union
    select customer_id, customer_name
    from customers_2024;
quit;

This query will return a list of unique customers from both tables, excluding any duplicate entries.

2. What is UNION ALL in PROC SQL?

The UNION ALL operator is similar to UNION, but with a key difference: it does not remove duplicates. When you use UNION ALL, it combines all the rows from multiple SELECT queries, including duplicates. This operator is typically faster than UNION because it does not require the extra step of removing duplicate rows.

Syntax of UNION ALL:

proc sql;
    select column1, column2
    from table1
    union all
    select column1, column2
    from table2;
quit;

In this example, UNION ALL will combine the data from table1 and table2 without eliminating any duplicates.

Example: Using UNION ALL for Sales Data

Suppose you have two tables: sales_2023 and sales_2024, each containing sales records for different years. You want to combine the records from both years, including duplicates, to analyze the total number of transactions.

proc sql;
    select sale_id, sale_date, sale_amount
    from sales_2023
    union all
    select sale_id, sale_date, sale_amount
    from sales_2024;
quit;

This query will return all sales records from both years, even if a sale appears in both sales_2023 and sales_2024.

3. Key Differences Between UNION and UNION ALL

While both UNION and UNION ALL are used to combine result sets, there are some important differences between the two:

Understanding these differences is crucial for deciding which operator to use, depending on your data analysis needs.

4. When to Use UNION in PROC SQL

You should use UNION when:

• You need to combine data from multiple tables and ensure that the result set only contains unique rows.
• You are working with datasets that might have overlapping records, and you want to avoid duplicate data in your analysis or reports.

Example: Using UNION for Unique Customer IDs

If you are combining customer IDs from multiple years and want to ensure that each customer appears only once, UNION is the appropriate choice:

proc sql;
    select customer_id
    from customers_2023
    union
    select customer_id
    from customers_2024;
quit;

This will give you a list of unique customer IDs across both years, without any duplicates.

5. When to Use UNION ALL in PROC SQL

You should use UNION ALL when:

• You need to combine data from multiple sources and want to preserve all records, even if they are duplicates.
• You are analyzing data where duplicates are meaningful, such as when you’re counting the total number of occurrences, sales, or events.

Example: Using UNION ALL for Total Sales Count

If you’re combining sales records from two years and need to count every sale, including duplicates, UNION ALL would be the best choice:

proc sql;
    select sale_id
    from sales_2023
    union all
    select sale_id
    from sales_2024;
quit;

This query will return all sales IDs, including those that appear in both years, giving you the total number of sales without removing duplicates.

6. Best Practices for Using UNION and UNION ALL in PROC SQL

When using UNION and UNION ALL, consider the following best practices:

1. Ensure Column Compatibility: Both queries in the UNION or UNION ALL operation must have the same number of columns and matching data types. If the columns are not compatible, you’ll encounter an error.
2. Use UNION for Clean Data: If you’re concerned about duplicate rows affecting your analysis or reports, always opt for UNION to ensure uniqueness.
3. Choose UNION ALL for Performance: If performance is a priority and duplicates are not a concern, UNION ALL will be faster, as it skips the de-duplication process.
4. Optimize for Large Datasets: When dealing with large datasets, using UNION ALL can reduce processing time, especially when duplicates are not a concern.

7. Advanced Use Cases for UNION and UNION ALL in PROC SQL

Both UNION and UNION ALL can be used in more complex scenarios, such as:

Example 1: Combining Data with Aggregation

You can combine data and then apply aggregation functions like SUM() or COUNT(). For example, if you want to calculate the total sales across two years:

proc sql;
    select sum(sale_amount) as total_sales
    from sales_2023
    union all
    select sum(sale_amount) as total_sales
    from sales_2024;
quit;

This query combines the total sales from two years, including any duplicate sales records, and gives you the total sum.

Example 2: Combining Data with Sorting

You can also use ORDER BY with UNION or UNION ALL to sort the combined result set.

proc sql;
    select customer_id, purchase_amount
    from purchases_2023
    union
    select customer_id, purchase_amount
    from purchases_2024
    order by customer_id asc;
quit;

This will combine data from the two tables and sort the result by customer_id in ascending order.

8. Conclusion

The UNION and UNION ALL operators in PROC SQL are powerful tools for combining datasets in SAS. While UNION is ideal for ensuring that the result set contains only unique rows, UNION ALL is better suited for performance when duplicates are acceptable or needed.

As a SAS professional, understanding the distinctions between these operators and knowing when to use each one can significantly enhance the efficiency and accuracy of your data analysis. By following best practices and considering performance, you can effectively combine data from multiple sources and streamline your workflows.

External Resources for Further Learning

1. SAS PROC SQL Documentation
2. SQL UNION vs UNION ALL
3. Advanced PROC SQL Techniques

Frequently Asked Questions (FAQs)

1. What is the difference between UNION and UNION ALL?

• UNION removes duplicates, while UNION ALL includes all rows, including duplicates.

1. Can I use UNION with different columns in PROC SQL?

• No, the number of columns and their data types must match in both queries.

1. When should I use UNION over UNION ALL?

• Use UNION when you need to eliminate duplicates in your result set.

1. Does UNION ALL improve query performance?

• Yes, UNION ALL is faster because it does not perform duplicate elimination.

1. Can I use UNION to combine more than two queries?

• Yes, you can combine more than two SELECT statements using UNION or UNION ALL.

1. What happens if the columns have different data types?

• The query will fail if the columns are not compatible in data types.

1. Can I use UNION with aggregation functions?

• Yes, you can combine data with aggregation functions like SUM() or COUNT().

1. What is the default sorting behavior with UNION?

• By default, the result set from UNION is sorted in ascending order.

1. How do I handle NULL values in UNION or UNION ALL?

• NULL values are treated as distinct values in both UNION and UNION ALL.

1. Can I use ORDER BY with UNION and UNION ALL?

• Yes, you can use ORDER BY to sort the combined result set after using UNION or UNION ALL.