SAS SQL

SAS provides extensive support for most popular relationship databases by using SQL queries in SAS programs. M ost ANSI SQL syntaxes are supported. T he procedure PROC SQL is used to process SQL statements. T his procedure not only returns the results of SQL queries, but also creates SAS tables and variables. E xamples of all of these situations are described below.

Grammar

The basic syntax for using PROC SQL in SAS is:

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

The following is a description of the parameters used:

• SQL queries are written after proC SQL statements, followed by QUAT statements.

Below we'll see how this SAS process can be used for CRUD (create, read, update, and delete) operations in SQL.

SQL creation operations

Using SQL, we can create new datasets in the form of raw data. I n the following example, you first declare a dataset called TEMP that contains the original data. T hen we write an SQL query to create a table from the variables of this dataset.

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 we execute the code above, we get the following results:

SQL read operations

Reading operations in SQL involve writing SQL SELECT queries to read data from a table. T he following program queries the SAS dataset called CARS available in the libraryASHELP. T he query gets some columns of the dataset.

PROC SQL;
SELECT make,model,type,invoice,horsepower
 FROM 
SASHELP.CARS
;
QUIT;

When we execute the code above, we get the following results:

SQL SELECT with WHERE Clause conditional query statement

The following program queries the CARS dataset using the where clause. I n the results, we only get observations as "Audi" and type "Sports".

PROC SQL;
SELECT make,model,type,invoice,horsepower
 FROM 
SASHELP.CARS
Where make = 'Audi'
and Type = 'Sports'
;
QUIT;

When we execute the code above, we get the following results:

SQL UPDATE operation

We can update the SAS table using the SQL Update statement. L et's start by creating a new table called EMPLOYEES2, and then update it with sql UPDATE statements.

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;

Remove the action in SQL

Deletion in SQL involves removing certain values from a table using SQL DELETE statements. L et's continue to use the data in the example above and remove rows from the table where the employee's salary is greater than 900.

PROC SQL;
DELETE FROM EMPLOYEES2
      WHERE SALARY > 900;
QUIT;
      PROC PRINT data = EMPLOYEES2;
RUN;