将 SAS 连接到 Microsoft SQL Server 数据库 [英] Connect SAS to a Microsoft SQL Server database

问题描述

我想将 SAS 连接到 Microsoft SQL Server 数据库，但没有成功.

I'd like to connect SAS to a Microsoft SQL Server database but I didn't succeed.

我已经有一个使用 NT 身份验证的数据库连接.

I already have a database connection using a NT authentication.

例如，当我想将 R(统计软件)连接到数据库时，我会这样做

For instance, when I want to connect R (the statistical sofware) to the db, I do that

ch_db <- odbcConnect("sql_nt")

ch_db <- odbcConnect("sql_nt")

但是当我在 SAS 中这样做时，它不起作用:

But when I do that in SAS, it doesn't work :

LIBNAME sql ODBC DSN='sql_nt';

LIBNAME sql ODBC DSN=’sql_nt’;

我收到此错误消息:

错误:未分配 Libname SQL.错误:LIBNAME 中的错误陈述.错误 22-7:无效的选项名称 SQL_NT.

ERROR: Libname SQL is not assigned. ERROR: Error in the LIBNAME statement. ERROR 22-7: Invalid option name SQL_NT.

我可能犯了一个愚蠢的错误，但我没有看到.

I probably do a stupid mistake, but I don't see it.

推荐答案

在 SAS 中制作一个 ODBC CONNECTION TO SQL SERVER;首先使用Windows ODBC 数据源管理器创建一个用户 DSN.我使用 SQL Server Native Client 和默认值.

In SAS to make a ODBC CONNECTION TO SQL SERVER; First make a User DSN using Windows ODBC Data Source Administrator. I use the SQL Server Native Client and the defaults.

然后在 SAS 中执行以下语句

THEN IN SAS EXECUTE THE FOLLOWING STATEMENT

libname mySasLib odbc datasrc='myUserDSN';

或者，从 SAS Explorer 窗口 GUI 中，选择新建以调用新建库对话框.

ALTERNATIVELY, from the SAS Explorer window GUI, choose New to invoke the New Library Dialog.

请注意，您计算机上的 DSN 源将列在数据源下拉列表中.

Note the DSN sources on your machine will be listed in the Data Source dropdown.

用户 ID、密码和选项字段是可选的，对于 Windows 集成安全性来说留空.

User ID, Password, and Options fields are optional and are left blank for Windows Integrated Security.

;

SUBSEQUENTLY--为了获得 SQL pass-through 的强大功能--这里是在 Work 中创建虚拟视图的语法；对于我的情况，这是一个令人难以置信的性能提升.

SUBSEQUENTLY--to get the power of SQL pass-through--here is the syntax for creating a virtual view in Work; this is an incredible performance boost for my situation.

proc sql;
connect to ODBC as mycon (datasrc='myUserDSN');
create view one as
select colA, colB from connection to mycon
(select colA, colB from tableInDataSrc order by colA);
disconnect from mycon;
quit;

然后是这样的:

proc univariate data=one;
by colA;
histogram colB;
run;

这篇关于将 SAS 连接到 Microsoft SQL Server 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！