SAS Certified Professional Prep Guide

Advanced Programming Using SAS 9.4

• Publisher: SAS Institute
• Year: 2019
• ISBN:
  9781642954692
• Topic: Computer Science
• Subtopic: Computer Science General

The official guide by the SAS Global Certification Program, SAS Certified Professional Prep Guide: Advanced Programming Using SAS 9.4 prepares you to take the new SAS 9.4 Advanced Programming Performance-Based Exam.

New in this edition is a workbook whose sample scenarios require you to write code to solve problems and answer questions. Answers to the chapter quizzes and solutions to the sample scenarios in the workbook are included. You will also find links to exam objectives, practice exams, and other resources such as the Base SAS Glossary and a list of practice data sets. Major topics include SQL processing, SAS macro language processing, and advanced SAS programming techniques.

All exam topics are covered in the following chapters:

SQL Processing with SAS

• PROC SQL Fundamentals
• Creating and Managing Tables
• Joining Tables Using PROC SQL
• Joining Tables Using Set Operators
• Using Subqueries
• Advanced SQL Techniques

SAS Macro Language Processing

• Creating and Using Macro Variables
• Storing and Processing Text
• Working with Macro Programs
• Advanced Macro Techniques

Advanced SAS Programming Techniques

• Defining and Processing Arrays
• Processing Data Using Hash Objects
• Using SAS Utility Procedures
• Using Advanced Functions

Practice Programming Scenarios (Workbook)

Chapter 1: PROC SQL Fundamentals

PROC SQL Basics

The PROC SQL SELECT Statement

The FROM Clause

The WHERE Clause

The GROUP BY Clause

The HAVING Clause

The ORDER BY Clause

PROC SQL Options

Validating Query Syntax

Quiz

End Notes

Last updated: October 16, 2019

PROC SQL Basics

What Is PROC SQL?

PROC SQL is the SAS implementation of Structured Query Language (SQL). SQL is a standardized language that is widely used to retrieve and update data in tables and in views that are based on those tables.

The following table compares terms that are used in data processing, SAS, and SQL.

This book uses all of these terms.

Data Processing	SAS	SQL
file	SAS data set	table or view
record	observation	row
field	variable	column

PROC SQL can often be used as an alternative to other SAS procedures or the DATA step. Use PROC SQL for tasks such as these:

• retrieve data from and manipulate SAS tables
• add or modify data values in a table
• add, modify, or drop columns in a table
• create tables and views
• join multiple tables (when they contain columns with the same name)
• generate reports

PROC SQL Syntax

The SQL procedure is initiated with a PROC SQL statement. You can use multiple statements within a PROC SQL step. Each statement defines a process and is executed immediately. Each statement must end with a semicolon. The SQL procedure is terminated with a QUIT statement.

Syntax, SQL procedure: PROC SQL <options>; statements; QUIT;

Last updated: October 16, 2019

The PROC SQL SELECT Statement

A Brief Overview

The SELECT statement retrieves and displays data. It consists of a SELECT clause and several optional clauses that can be used within the SELECT statement. Each clause begins with a keyword and is followed by one or more components. The optional clauses name the input data set, subset, group, or sort the data.

A PROC SQL step that contains one or more SELECT statements is referred to as a PROC SQL query. The SELECT statement is only one of several statements that can be used with PROC SQL.

SELECT Statement Syntax

The SELECT statement is the primary tool of PROC SQL. Using the SELECT statement, you can identify, manipulate, and retrieve columns of data from one or more tables and views. The SELECT statement must contain a SELECT clause and a FROM clause, both of which are required in a PROC SQL query.

Syntax, SELECT statement: PROC SQL <options>; SELECT column-1 <,...column-n> FROM input-table <WHERE clause> <GROUP BY clause> <HAVING clause> <ORDER BY clause> ; QUIT;

When you construct a SELECT statement, you must specify the clauses in the following order:

• The SELECT clause selects columns.
• The FROM clause selects one or more source tables or views.
• The WHERE clause enables you to filter your data.
• The GROUP BY clause enables you to process data in groups.
• The HAVING clause works with the GROUP BY clause to filter grouped results.
• The ORDER BY clause specifies the order of the rows.

Example: Selecting Columns

To specify which columns to display in a query, write a SELECT clause. After the keyword SELECT, list one or more column names and separate the column names with commas. The SELECT clause specifies existing columns and can create columns. The existing columns are already stored in a table.

The following SELECT clause specifies the columns EmpID, JobCode, Salary, and bonus. The columns EmpID, JobCode, and Salary are existing columns. The column named Bonus is a new column. The column alias appears as a column heading in the output and matches the case that you used in the SELECT clause.

proc...