When working with SAS on the mainframe, one of the most powerful tools you have is the PROC SQL procedure. Unlike traditional DATA steps or sorting workflows, PROC SQL lets you query, join and manipulate large datasets directly, often with fewer steps and greater resource efficiency.
In this guide, you’ll learn how to use PROC SQL effectively in a mainframe context: from basic syntax to join operations, performance considerations and best practices. Whether you’re new to SAS on z/OS or expanding your batch-analytics toolkit, this tutorial will help you master SQL-style data operations in a mainframe environment.
PROC SQL is a built-in SAS procedure that allows you to write SQL-style statements inside SAS programs. It can be an alternative to SAS procedure or DATA steps when used efficiently.
To being with, we have extended the SAS program to take as inputs two files (with one common record) and will perform a SQL select operation on the two files. We will use PROC SQL statement to create internal SAS table and do a join on the DELIVERY_NR.
The output will be the common record in two record sets:
IF you use the FEEDBACK keyword on PROC SQL, it will display the column names in the SAS LOG.
Common Use Cases for PROC SQL on a Mainframe
Data cleansing and deduplication: Use DISTINCT, GROUP BY, or conditional logic in SQL rather than multi-step SAS merges.
Joining multiple datasets: Combining flat files, VSAM files or SAS datasets on z/OS without performing pre-sorts.
Summarising high-volume data: Using GROUP BY, SUM, COUNT, AVG to produce summary reports.
Creating views or subsets for reporting: Use CREATE VIEW to present filtered datasets without duplicating data.
Conclusion
If you’re working with SAS on a mainframe (z/OS), mastering PROC SQL gives you a powerful tool for querying, joining and manipulating large datasets efficiently. By reducing the need for multiple steps (such as sorting, merging, filtering separately), you can streamline your batch workflows and save on mainframe resources.
Leave a comment