SAS-SQL
SAS provides comprehensive support in most popular relational databases by using SQL queries within the SAS program. It supports most of the ANSI SQL syntax. We can process the SQL queries by using the procedure PROC SQL. This procedure not only returns the results of the SQL query but can also create SAS tables and variables.
All SQL related scenarios are illustrated below.
Syntax:
Where,
- PROC SQL: It is the procedure under which the SQL query is written.
- QUIT: It is the statement which is used to close the SQL query.
Following are the SQL operations in which we will learn how to use PROC SQL procedure for CRUD (CREATE, READ, UPDATE, and DELETE) operations in SQL.
Create Operation
We can create a new data set from raw data by using SQL. Let’s consider the SAS data set DETAILS, which contains personal information of employees of an organization. Now, we need to write the SQL query to create a table EMPLOYEE from the raw data of variables.
Execute the above code in SAS studio:
Output:
As we can see in the output, the table has been created for all the variables.
SQL Read Operation
In the Read Operation of SQL, we can read the data from the table. To read the data, we need to write a SQL SELECT query. In the example, we are going to read the data from table CARS which is already available in the SASHELP Library. The SELECT query fetches the data of columns (or variables) mentioned in the data set.
Example:
Execute the above code in SAS studio:
Output:
As you can see in the output, all the data of table CARS has been displayed.
SQL SELECT Query With WHERE Clause
SAS uses the “where” clause in the sub-setting of the data, i.e., only to get the values which we required and to abandon the remaining values.
The program below has a CARS data set with a clause where. In the result, we will get only the observations of make column with the value “Audi” and of type column with the value “Sports.”
Execute the above code in SAS studio:
Output:
As you can see in the output, all the observations of columns Make=Audi and Type= Sports have been displayed, and the rest have been omitted.
SQL UPDATE Operation
In the Update Operation of SQL, we can update the data of the table. To update the data, we need to write SQL UPDATE query. In the below example, we are going to update the data of data set DETAILS.
Execute the above code in SAS Studio:
Output:
As you can see in the output, all variables have been updated such as empid as id, name as empname and dept as department.
SQL DELETE Operation
In the Delete operation of SQL, we can delete specific data from the table. To delete the data from the table, we need to write SQL DELETE query. In the below example, we are going to delete the data from the table.
Here we are taking the data from the above example and will delete the rows from the table in which the salary of employees is more than 30,000.
Execute the above code in SAS Studio:
Output:
As you can see in the output, all employees whose salary is more than 30,000 have been deleted.