SAS Global Forum 2012 Programming - Paper 251-2012 - Exploring DATA Step Merges and PROC SQL Joins

Oct 25, 2026

Upload by Finn

Explore the various DATA step merge and PROC SQL join processes, highlighting similarities and differences through practical examples. The presentation examines core objectives and principles for merging and joining tables, including coding constructs for inner and outer merges/joins and related hash techniques. Guidance covers conditional logic scenarios and best practices to choose effective approaches when combining SAS tables for insights into data relationships.

LanguageEnglish

CategoryTechnology

FormatPDF

Pages14

Reading Time35 min

AccessFree to download

Abstract

Introduction

Example Tables

The Process of Merging and Joining

Contrasting Merges and Joins

Cartesian Product

Code

Results

SAS Global Forum 2012 Programming: Foundations and Fundamentals Paper 251-2012 Exploring DATA Step Merges and PROC SQL Joins Kirk Paul Lafler, Software Intelligence Corporation , Spring Valley, California Abstract Explore the various DATA step merge and PROC SQL join processes. This presentation examines the similarities and differences between each, and provides examples of effective coding techniques. Attendees examine the objectives and principles behind merging and joining, as well as the coding constructs associated with inner and outer merges and joins, and hash techniques. Introduction This paper illustrates the similarities and differences between the Base-SAS® software DATA step merge and PROC SQL join techniques. We’ll examine two “key” topics that most users are confronted with when working with their tables of data, conditional logic scenarios and merges/joins. This paper introduces brief explanations, guidelines and“simple” techniques for users to consider when confronted with conditional logic scenarios and merges/joins. You are encouraged to explore these and other techniques to make your SAS® experience an exciting one. Example Tables The data used in all the examples in this paper consist of a selection of movies that I’ve viewed over the years. The Movies table contains four character columns: title, category, studio, and rating , and two numeric columns: length and year. The data stored in the Movies table is shown in Figure 1 below. Figure 1. MOVIES Table The data stored in the ACTORS table consists of three columns: title, actor_leading, and actor_supporting, all of which are defined as character columns. The data stored in the Actors table is illustrated in Figure 2 below. SAS Global Forum 2012 Programming: Foundations and Fundamentals The Process of Merging and Joining A merge or join is the process of combining two or more tables’ side-by-side (horizontally) . Its purpose is to gather and manipulate data from across tables for exciting insights into data relationships. The process consists of a matching process between a table’s rows bringing together some or all of the tables’ contents, as illustrated in Figure 3. Table One Table Two Table Three . . . Figure 3. The Process of Merging and Joining Tables The ability to define relationships between multiple tables and retrieve information based on these relationships is a powerful feature of the relational model. A merge or join of two or more tables provides a means of gathering and manipulating data. Merges and joins are specified on a minimum of two tables at a time, where a column from each table is used for the purpose of connecting the two tables. Connecting columns should have "like" values and the same column attributes since the processes’ success is dependent on these values. Contrasting Merges and Joins The difference between a DATA step merge and a join are subtle, but differences do exist. Merge Features 1. Relevant only to the SAS System – not portable to other vendor data bases. 2. More steps are often needed than with the SQL procedure. 3. Data must first be sorted using by-value. 4. Requires common variable name. 5. Duplicate matching column is automatically overlaid. 6. Results are not automatically printed. Join Features 1. Portable to other vendor data bases. 2. Data does not need to be sorted using BY-value. 3. Does not require common variable name. 4. Duplicate matching column is not automatically overlaid. 5. Results are automatically printed unless NOPRINT option is specified. Cartesian Product A Cartesian Product is defined as a result set of all the possible rows and columns contained in two or more data sets or tables. The DATA step doesn’t really lend itself to easily creating a Cartesian Product – PROC SQL is the desired approach. Its most noticeable coding characteristic is the absence of a WHERE-clause. The resulting set of data resulting from a Cartesi

FAQs

What is the main goal of merging and joining tables in SAS?

Merging and joining combine rows side-by-side to gather and manipulate data across tables, enabling insights into relationships between data.

What are key differences between a DATA step merge and a PROC SQL join?

A DATA step merge requires sorting by BY-value and common variable names, and it overprints duplicate matching columns, while a PROC SQL join is portable, does not require sorting, and does not require common variable names.

How does a Cartesian Product join relate to PROC SQL and the DATA step?

A Cartesian Product produces all possible combinations of rows and columns from multiple tables; PROC SQL is the preferred approach because it uses coding characteristics like the absence of a WHERE clause and can yield very large result sets.

Oct 25, 2026

Upload by Finn