36、探索SAS数据仓库与其他系统的连接及数据交互

探索SAS数据仓库与其他系统的连接及数据交互

1. SQL环境下的数据预览与复制

在SQL接口中，我们可以设置并保存视图，通过行和列的筛选条件，动态展示从源数据中筛选后要下载的数据集的样子。每次连接并运行该视图时，它会显示源系统中当前状态的数据。通过在实际下载数据之前通过视图查看预期的数据提取，我们可以估计将视图结果复制到SQL环境并修改视图标准以满足需求所需的工作量。

使用 CREATE TABLE 命令将数据从远程SQL环境的视图复制到本地SQL环境的硬编码表中。由于视图仅提供数据的查看而不将数据集实际保存到磁盘，运行视图所需的处理时间非常少。因此，在复制任何数据之前，使用SQL选择查询在远程SQL系统中准备视图是更可取的做法。程序员将传输的数据限制为视图中返回的记录，一旦设置了最佳视图，就使用 CREATE TABLE 命令将视图中表示的实际数据从远程系统复制到本地SQL系统的表中。

2. 使用SAS从远程数据系统复制数据

SAS程序员使用 PROC SQL 可以完成与SQL程序员相同的连接到远程系统并传输表的操作。具体步骤如下：
1. 配置ODBC连接 ：在SAS中，需要实际设置一个连接，以便对其进行配置并为要连接的环境设置参数。
2. 创建引用远程环境的库名 ： LIBNAME 不仅可以为服务器上的目录创建别名，还可以为远程环境中的位置创建别名。这样，如果远程服务器有一个包含表的SQL数据库，我们就可以使用库名来引用特定的表。
3. 创建注册 ：如果要为服务器上的多个SAS用户设置连接，需要进行创建注册这一步骤。注册用户、ODBC服务器和ODBC库是在SAS中完成的任务，这些操作主要是为了帮助SAS与远程数据存储协同工作。

一旦建立了SAS与其他数据库环境之间的连接，就可以使用该连接访问远程环境中的数据。具体操作步骤如下：
1. 使用ODBC连接到远程环境 ：使用ODBC在SAS中进行与SQL类似的操作，首先要建立ODBC连接。假设我们使用SAS ODBC连接到远程SQL服务器，在此过程中，将创建一个引用远程环境的库名。
2. 使用连接中建立的库名引用远程数据库环境中的表 ：例如，连接到医疗系统数据库，假设该环境的库名为 other ，远程数据库中有一个名为 facilities 的表，在SAS代码中可以使用 other.facilities 来查询该表。
3. 使用 PROC SQL 或数据步命令将数据从远程环境复制到SAS环境 ：虽然可以使用数据步命令，但 PROC SQL 命令往往更易于使用，因为它们是专门为跨网络从数据存储复制表等过程设计的。

例如，将整个 facilities 表复制到SAS环境的 WORK 目录中，使用数据步的代码如下：

data work.facilities;
    set other.facilities;
RUN;

使用 PROC SQL 的代码如下：

PROC SQL;
    CREATE TABLE work.facilities as
        SELECT * FROM other.facilities;
QUIT;

3. 利用PROC SQL视图进行数据传输

SAS中的 PROC SQL 和SQL一样，具有创建视图的能力，这些视图可以帮助将非SAS系统的数据传输到SAS系统中。视图就像一个存储的查询，本身不包含数据，而是存储了一个算法或公式。当运行视图时，这个保存的算法或公式会根据条件动态过滤底层表中的数据。

存储视图在SAS服务器上占用的空间比保存实际数据表（如查询的硬编码结果）要少得多。并且，动态执行视图的结果可以像表一样使用，可以与表和其他视图进行连接或合并。因此，在SAS数据仓库中存储 PROC SQL 视图是组织从不同格式的外部数据库定期向SAS仓库传输数据的好方法。

假设一个场景，佛罗里达州医疗保健管理局（AHCA）维护一个SQL数据仓库，而我们的仓库是SAS的。我们获得了连接到AHCA系统的权限，每月的工作是登录AHCA系统并传输上个月医疗设施的计费记录数据。使用ODBC连接、 PROC SQL 视图和SAS宏，我们可以完成以下任务来将指定月份的数据从AHCA的非SAS存储传输到我们的SAS环境：
1. 使用 SAS/ACCESS 组件运行SAS代码，建立到AHCA仓库的ODBC连接，以便查询AHCA SQL数据库中的表。
2. 调用本地SAS宏，设置变量的值以指定特定的时间段。例如，使用 %LET 命令设置 xfer_month 变量为要传输数据的月份（1表示一月）， xfer_year 变量为要传输数据的年份（如2018）。
3. 一旦SAS宏接受了变量值，它应该使用 WHERE 子句中的查询条件中的变量值，在AHCA的远程数据库表上运行存储的 PROC SQL 视图。当查询AHCA数据库中包含日期的表时，可以通过 PROC SQL 命令中 WHERE 子句引用的 %LET 宏变量表示的条件来过滤记录日期的月份和年份。
4. 使用 PROC SQL 的 CREATE TABLE 命令将视图过滤后的数据复制到本地SAS数据仓库的数据表中。

以下是实现该场景的代码示例：

%LET xfer_month = 1;
%LET xfer_year = 2018;
PROC SQL;
    CREATE VIEW AS WORK.recent_billing
        SELECT *
        FROM other.billing
        WHERE billing_month = &xfer_month and
              billing_year = &xfer_year;
QUIT;

代码解释如下：
1. 使用 %LET 将 xfer_month 宏变量设置为1（表示一月）， xfer_year 设置为2018。这些宏变量将在后续创建的 PROC SQL 视图的 WHERE 子句中使用。
2. 调用 PROC SQL ，代码以 PROC SQL 开始，以 QUIT 结束。
3. 在 PROC SQL 代码中，在 WORK 目录中创建一个名为 recent_billing 的视图。通过从远程数据库的 other.billing 表中选择所有列（ SELECT * ）来实现。
4. WHERE 条件中，假设 other.billing 表有 billing_month （表示账单的数字月份，如1表示一月）和 billing_year （表示年份，如2018）两个变量。使用 & 符号表示使用 %LET 语句中设置的宏变量的值。

运行上述代码创建 recent_billing 视图后，我们可以设置宏变量并查看要传输的数据。还可以在后续的 PROC SQL 代码中通过视图名称引用该视图，将视图结果从远程数据库复制到SAS数据仓库。以下是复制视图结果的代码：

PROC SQL;
    CREATE TABLE AS WORK.billing_2018_01;
        SELECT *
        FROM WORK.recent_billing;
QUIT;

运行此代码后， 2018_01_billing 表将保存在 WORK 目录中，它代表了运行 WORK.recent_billing 视图的结果，该视图使用 %LET 语句设置的宏变量创建了过滤器。不过，使用这种方法从远程数据库提取的表可以看作是原始数据提取，虽然 PROC SQL 视图允许我们在一定程度上自定义传输的表，但它们仍需要进行ETL以纳入SAS仓库结构。

4. 数据步视图

虽然本节重点介绍在 PROC SQL 中创建视图，但从技术上讲，也可以使用SAS数据步创建视图。然而，这些视图有几个重要的缺点，包括运行效率比操作硬编码表的数据步低、不支持 PROC SORT 以及不能在SAS会话外保存。因此， PROC SQL 视图在SAS数据仓库中非常有用，是本节的重点。

下面是一个简单的流程图，展示了使用SAS从远程SQL数据库提取数据的主要步骤：

graph LR
    A[配置ODBC连接] --> B[创建引用远程环境的库名]
    B --> C[创建注册（可选）]
    C --> D[使用ODBC连接到远程环境]
    D --> E[使用库名引用远程表]
    E --> F[使用PROC SQL或数据步复制数据]

通过以上步骤和方法，我们可以有效地实现SAS数据仓库与其他系统的数据交互和传输。

探索SAS数据仓库与其他系统的连接及数据交互

5. 导出SAS数据到非SAS数据存储

前面介绍了从远程SQL数据库提取数据到SAS环境，现在来看相反的情况，即连接到远程SQL数据库并将SAS数据导出到该SQL数据库。这里主要介绍使用 PROC SQL 的方法。

假设远程AHCA数据库名为 AHCA_database ，我们设置一个库名 other 来引用它：

LIBNAME other ODBC NOPROMPT="DSN=AHCA_database";

使用 PROC SQL 将SAS环境 WORK 目录中的 claims 数据表导出到AHCA数据库，并添加一个空白的10字符列 blank_column 的代码如下：

PROC SQL;
    CONNECT TO ODBC (DATABASE= AHCA_database);
    CREATE TABLE other.claims as SELECT * FROM WORK.claims;
    EXECUTE (ALTER TABLE other.claims ALTER COLUMN blank_column CHAR(10)) BY ODBC;
    DISCONNECT FROM ODBC;

下面详细解释这段代码的操作步骤：
1. 调用 PROC SQL 并建立连接 ：首先调用 PROC SQL ，然后使用ODBC建立连接。由于ODBC可以连接多个远程数据库， DATABASE= AHCA_database 指定了要连接的数据库。
2. 创建远程表 ： CREATE TABLE other.claims as SELECT * FROM WORK.claims 这行代码在远程SQL数据库（映射到 other 库名）中创建一个名为 claims 的表，该表的数据来自本地SAS环境 WORK 目录中的 claims 表。
3. 使用 SAS/ACCESS 传递设施 ： EXECUTE (ALTER TABLE other.claims ALTER COLUMN blank_column CHAR(10)) BY ODBC 这行代码展示了 SAS/ACCESS 传递设施的使用。 EXECUTE 命令是该设施语言的一部分，括号内使用类似 PROC SQL 的语言创建一个 ALTER TABLE 命令，为远程 claims 表添加一个10字符的空白列 blank_column 。
4. 断开连接 ： DISCONNECT FROM ODBC 命令用于断开与远程数据库的连接。

这个例子表明可以将SAS数据库中的表导出到SQL数据库，但一般来说，程序员更常见的操作是连接到远程数据库以将数据提取到本地SAS数据库，而不是将本地数据推送到远程数据库。

6. SAS在报告功能集成方面的创新

最初设计SAS时，网络环境是封闭的，所有功能（存储、ETL和报告）都必须在该环境内完成。因此，虽然SAS主要专注于数据管理，但也开发了能在SAS环境中工作的报告工具。然而，这些工具并不复杂，因为SAS的目标是数据管理而非数据报告。

随着时间的推移，通过互联网实现系统间的连接变得可行且成为标准。这为SAS提供了专注于其擅长的数据存储和管理的机会，同时可以使用其他优化的报告产品来报告SAS数据。

以下是一个表格，总结了SAS与其他系统数据交互的主要操作及方法：
|操作类型|操作方法|具体步骤|
| ---- | ---- | ---- |
|从远程SQL数据库提取数据到SAS|使用 PROC SQL 和ODBC|1. 配置ODBC连接；2. 创建引用远程环境的库名；3. 创建注册（可选）；4. 使用ODBC连接到远程环境；5. 使用库名引用远程表；6. 使用 PROC SQL 或数据步复制数据|
|将SAS数据导出到远程SQL数据库|使用 PROC SQL |1. 配置库名引用远程数据库；2. 调用 PROC SQL 并建立连接；3. 创建远程表；4. 使用 SAS/ACCESS 传递设施进行表结构修改；5. 断开连接|

下面是一个流程图，展示了将SAS数据导出到远程SQL数据库的主要步骤：

graph LR
    A[配置库名引用远程数据库] --> B[调用PROC SQL并建立连接]
    B --> C[创建远程表]
    C --> D[使用SAS/ACCESS传递设施修改表结构]
    D --> E[断开连接]

综上所述，通过合理运用 PROC SQL 、ODBC连接、视图和宏等工具，我们可以实现SAS数据仓库与其他系统之间的数据交互和传输，并且可以根据不同的需求进行数据的提取和导出操作。同时，随着技术的发展，SAS在数据管理和报告功能集成方面也有了新的发展和应用方向。