SAS Tutorial | PROC SQL Programming Techniques for SAS Users and Programmers

Structured Query Language (SQL) is a universal language used in data science, data analytics, statistics, data management and other disciplines to access, transform, manipulate and output data stored in SAS data sets, relational databases and tables. Based on Kirk Paul Laflers new PROC SQL: Beyond the Basics Using SAS®, Third Edition book, this SAS tutorial presents core concepts and techniques on how to use PROC SQL as a programming and database language. Attendees learn how to construct powerful and scalable queries; define, access and manipulate data from one or more tables using PROC SQL; subset, order and group data; summarize data down rows and across columns using summary functions; join two or more tables using conventional (equi-joins) and unconventional (left, right and full outer join) methods; apply effective fuzzy matching programming techniques when a tables key(s) are inconsistent or less than reliable; use the SQL-macro interface to create single-value (or aggregate) and value-list (array) macro variables; construct real-world queries including nearest neighbor processing; first, last and between By-group processing; produce customized output using Output Delivery System (ODS); construct powerful and scalable indexes to dynamically access rows of data; and query performance considerations.

Session ID: 5308
Presenter: Kirk Paul Lafler, SAS Consultant, Application Developer, Programmer, Data Analyst, Educator and Author, Software Intelligence Corporation
Topic: Programmer
Industry: Non-Industry Specific
Audience: Programmer
Level: N/A

Content Outline
00:00:00 – Introduction and Instructor bio
00:00:36 – Tutorial objectives
00:02:08 – Order of PROC SQL SELECT clauses
00:06:47 – Overview of Join Types
00:17:05 – FIRST., LAST., and BETWEEN processing
00:31:11 – Nearest Neighbor processing
00:39:56 – Summary Take-aways from first section
00:47:21 – PROC SQL Join Algorithms
00:52:45 – Displaying SQL Execution plan: _METHOD option
00:56:58 – Codes with brief descriptions slide
00:57:20 – OPTIONS MSGLEVEL=I;
00:58:46 – Summary Take-aways from second section
01:00:58 – Fuzzy matching techniques using PROC SQL
01:31:00 – Constructing logic scenarios with case expressions
01:35:34 – Summary Take-aways from third section
01:36:34 – Index rules, strategies and usage
01:52:20 – Summary Take-aways from section four
01:54:56 – PROC SQL – Macro Interface
02:00:55 – Data-driven Programming Techniques
02:06:40 – Summary Take-aways from section five
02:07:40 – Conclusion

Learn more about SAS Software

SUBSCRIBE TO THE SAS USERS YOUTUBE CHANNEL #SASUsers #LearnSAS #SASGF

ABOUT SAS
SAS is a trusted analytics powerhouse for organizations seeking immediate value from their data. A deep bench of analytics solutions and broad industry knowledge keep our customers coming back and feeling confident. With SAS®, you can discover insights from your data and make sense of it all. Identify what’s working and fix what isn’t. Make more intelligent decisions. And drive relevant change.

CONNECT WITH SAS

[zimcomm]

Very comprehensive Proc SQL lecture indeed

[louisehadden]

Great tutorial! No matter how long I've been programming I find a careful explanation of SQL syntax and joins extraordinarily helpful. Instead of copying and pasting code that has worked in the past it makes me think!

[sheetalsworldofrecipes]

Thank you Kirk, for this tutorial, it is great help to me

[ubsvks]

This is an awesome video. Thank you very much.

[ashutoshsingh-osgn]

From where we can get the sample dataset to follow along the training