Sas 简明教程

SAS - SQL

SAS 通过在 SAS 程序中使用 SQL 查询，为大多数常用的关系型数据库提供广泛的支持。大多数的 ANSI SQL 语法受到支持。使用 PROC SQL 过程来处理 SQL 语句。这个过程不仅可以发送 SQL 查询的结果，还可以创建 SAS 表与变量。将针对所有这些场景提供示例，如下所示。

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

在 SAS 中使用 PROC SQL 的基本语法如下：

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 −

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

接下来，我们将看到如何将这个 SAS 过程用于在 SQL 中的 CRUD （创建、阅读、更新和删除）操作。

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

使用 SQL，我们可以从原始数据创建新数据集。在以下示例中，我们首先声明了一个包含原始数据的 TEMP 数据集。然后，我们编写一个 SQL 查询，根据这个数据集中的变量创建表。

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 create table

SQL Read Operation

SQL 中的读取操作包括编写 SQL SELECT 查询，以从表中读取数据。在以下示例中，此程序查询位于 SASHELP 库中的名为 CARS 的 SAS 数据集。查询获取了此数据集的一些列。

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 −

sql1

SQL SELECT with WHERE Clause

以下此程序使用 where 子句查询 CARS 数据集。在结果中，我们只得到一个将制造商指定为“Audi”，且类型指定为“运动”的记录。

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 where clause

SQL UPDATE Operation

我们可以使用 SQL 更新语句来更新 SAS 表。下面我们首先创建一个名为 EMPLOYEES2 的新表，然后使用 SQL UPDATE 语句来更新该表。

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 −

proc sql update

SQL DELETE Operation

SQL 中的删除操作涉及到使用 SQL DELETE 语句从表中删除某些值。我们继续使用上述示例中的数据，并删除表中员工工资大于 900 的行。

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;

在执行以上代码之后，我们将得到以下结果：

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

proc sql delete