SAS offers extensive support to most of the popular relational databases by using SQL queries inside SAS programs. Most of the ANSI SQL syntax is supported. The procedure PROC SQL is used to process the SQL statements. This procedure can not only give back the result of an SQL query, it can also create SAS tables & variables. The example of all these scenarios is described below.

Syntax

The basic syntax for using PROC SQL in SAS is −

PROC SQL;
SELECT Columns
FROM TABLE
WHERE Columns
GROUP BY Columns
;
QUIT;

Following is the description of the parameters used −

the SQL query is written below the PROC SQL statement followed by the QUIT statement.

Below we will see how this SAS procedure can be used for the CRUD (Create, Read, Update and Delete)operations in SQL.

SQL Create Operation
Using SQL we can create new data set form raw data. In the below example, first we declare a data set named TEMP containing the raw data. Then we write a SQL query to create a table from the variables of this data set.
DATA TEMP;
INPUT ID $ NAME $ SALARY DEPARTMENT $;
DATALINES;
1 Rick 623.3 IT

2 Dan 515.2 Operations

3 Michelle 611 IT

4 Ryan 729 HR

5 Gary 843.25 Finance

6 Nina 578 IT

7 Simon 632.8 Operations

8 Guru 722.5 Finance

;

RUN;


PROC SQL;

CREATE TABLE EMPLOYEES AS

SELECT * FROM TEMP;

QUIT;

PROC PRINT data = EMPLOYEES;

RUN;

When the above code is executed we get the following result −

SQL Read Operation

The Read operation in SQL involves writing SQL SELECT queries to read the data from the tables. In The below program queries the SAS data set named CARS available in the library SASHELP. The query fetches some of the columns of the data set.

PROC SQL;

SELECT make,model,type,invoice,horsepower

FROM

SASHELP.CARS

;

QUIT;

When the above code is executed we get the following result −

SQL SELECT with WHERE Clause

The below program queries the CARS data set with a where clause. In the result we get only the observation which have make as ‘Audi’ and type as ‘Sports’.

PROC SQL;

SELECT make,model,type,invoice,horsepower

FROM

SASHELP.CARS

Where make = 'Audi'

and Type = 'Sports'

;

QUIT;

When the above code is executed we get the following result −

SQL UPDATE Operation

We can update the SAS table using the SQL Update statement. Below we first create a new table named EMPLOYEES2 and then update it using the SQL UPDATE statement.

DATA TEMP;

INPUT ID $ NAME $ SALARY DEPARTMENT $;

DATALINES;

1 Rick 623.3 IT

2 Dan 515.2 Operations

3 Michelle 611 IT

4 Ryan 729 HR

5 Gary 843.25 Finance

6 Nina 578 IT

7 Simon 632.8 Operations

8 Guru 722.5 Finance

;

RUN;




PROC SQL;

CREATE TABLE EMPLOYEES2 AS

SELECT ID as EMPID,

Name as EMPNAME ,

SALARY as SALARY,

DEPARTMENT as DEPT,

SALARY*0.23 as COMMISION

FROM TEMP;

QUIT;




PROC SQL;

UPDATE EMPLOYEES2

SET SALARY = SALARY*1.25;

QUIT;

PROC PRINT data = EMPLOYEES2;

RUN;

When the above code is executed we get the following result −

SQL DELETE Operation

The delete operation in SQL involves removing certain values from the table using the SQL DELETE statement. We continue to use the data from the above example and delete the rows from the table in which the salary of the employees is greater than 900.

PROC SQL;

DELETE FROM EMPLOYEES2

WHERE SALARY > 900;

QUIT;

PROC PRINT data = EMPLOYEES2;

RUN;

So, this brings us to the end of blog. This Tecklearn ‘SAS – SQL’ blog helps you with commonly asked questions if you are looking out for a job in SAS. If you wish to learn SAS and build a career in Data Analytics domain, then check out our interactive, SAS Training for SAS BASE Certification Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/sas-training-for-sas-base-certification/

SAS Training for SAS BASE Certification Training

About the Course

SAS Certification Training is intended to make you an expert in SAS programming and Analytics. You will be able to analyse and write SAS code for real problems, learn to use SAS to work with datasets, perform advanced statistical techniques to obtain optimized results with Advanced SAS programming. In this SAS online training course, you will also learn SAS macros, Machine Learning, PROC SQL, procedure, statistical analysis and decision trees. You will also work on real-life projects and prepare for the SAS Certified Base Programmer certification exam. Upon the completion of this SAS online training, you will have enough proficiency in reading spreadsheets, databases, using SAS functions for manipulating this data and debugging it.

Why Should you take SAS Training?

The average salary for a Business Intelligence Developer skilled in SAS is $100k (PayScale salary data)
SAS, Google, Facebook, Twitter, Netflix, Accenture & other MNCs worldwide are using SAS for their Data analysis activities and advance their existing systems.
SAS is a Leader in 2017 Gartner Magic Quadrant for Data Science Platform.

What you will Learn in this Course?

Introduction to SAS

Introduction to SAS
Installation of SAS
SAS windows
Working with data sets
Walk through of SAS windows like output, search, editor etc

SAS Enterprise Guide

How to read and subset the data sets
SET Statement
Infile and Infile Options
SAS Format -Format Vs Informat

SAS Operators and Functions

Using Variables
Defining and using KEEP and DROP statements
Output Statement
Retain Statement
SUM Statement

Advanced SAS Procedures

PROC Import
PROC Print
Data Step Vs Proc
Deep Dive into Proc

Customizing Datasets

SAS Arrays
Useful SAS Functions
PUT/INPUT Functions
Date/Time Functions
Numeric Functions
Character Functions

SAS Format and SAS Graphs

SAS Format statements
Understanding PROC GCHART, various graphs, bar charts: pie, bar

Sorting Techniques

NODUP
NODUKEY
NODUP Vs NODUKEY

Data Transformation Function

Character functions, numeric functions and converting variable type
Use functions in data transformation

Deep Dive into SAS Procedures, Functions and Statements

Find Function
Scan Function
MERGE Statement
BY Statement
Joins
Procedures Vs Function
Where Vs If
What is Missover
NMISS
CMISS

PROC SQL

SELECT statement
Sorting of Data
CASE expression
Other SELECT statement clauses
JOINS and UNIONS

Using SAS Macros

Benefits of SAS Macros
Macro Variables
Macro Code Constituents and Macro Step
Positional Parameters to Macros

Got a question for us? Please mention it in the comments section and we will get back to you.

555