将 SAS 连接到 Microsoft SQL Server 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16697362/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connect SAS to a Microsoft SQL Server database
提问by Ricol
I'd like to connect SAS to a Microsoft SQL Server database but I didn't succeed.
我想将 SAS 连接到 Microsoft SQL Server 数据库，但没有成功。
I already have a database connection using a NT authentication.
我已经有一个使用 NT 身份验证的数据库连接。
For instance, when I want to connect R (the statistical sofware) to the db, I do that
例如，当我想将 R（统计软件）连接到数据库时，我会这样做
ch_db <- odbcConnect("sql_nt")
ch_db <- odbcConnect("sql_nt")
But when I do that in SAS, it doesn't work :
但是当我在 SAS 中这样做时，它不起作用：
LIBNAME sql ODBC DSN='sql_nt';
LIBNAME sql ODBC DSN='sql_nt';
I have this error message :
我有这个错误信息：
ERROR: Libname SQL is not assigned. ERROR: Error in the LIBNAME statement. ERROR 22-7: Invalid option name SQL_NT.
错误：未分配 Libname SQL。错误：LIBNAME 语句中的错误。错误 22-7：无效的选项名称 SQL_NT。
I probably do a stupid mistake, but I don't see it.
我可能犯了一个愚蠢的错误，但我没有看到。
回答by subsci
In SAS to make a ODBC CONNECTION TO SQL SERVER; First make a User DSNusing Windows ODBC Data Source Administrator. I use the SQL Server Native Client and the defaults.
在SAS中做一个ODBC CONNECTION TO SQL SERVER；首先使用Windows ODBC 数据源管理器创建一个用户 DSN。我使用 SQL Server Native Client 和默认值。
THEN IN SAS EXECUTE THE FOLLOWING STATEMENT
然后在 SAS 中执行以下语句
libname mySasLib odbc datasrc='myUserDSN';
ALTERNATIVELY, from the SAS Explorer window GUI, choose Newto invoke the New Library Dialog.
或者，从 SAS Explorer 窗口 GUI，选择新建以调用新建库对话框。
Note the DSN sources on your machine will be listed in the Data Sourcedropdown.
请注意，您计算机上的 DSN 源将列在“数据源”下拉列表中。
User ID, Password, and Options fields are optional and are left blank for Windows Integrated Security.
“用户 ID”、“密码”和“选项”字段是可选的，对于 Windows 集成安全性，这些字段留空。
;
;
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.
后续——为了获得 SQL pass-through 的强大功能——这里是在 Work 中创建虚拟视图的语法；对于我的情况，这是一个令人难以置信的性能提升。
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;
Then something like:
然后是这样的：
proc univariate data=one;
by colA;
histogram colB;
run;
回答by Joe
Take a look at this page: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm
看看这个页面：http: //support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm
Specifically, I think you should try it this way:
具体来说，我认为你应该这样尝试：
libname mydblib odbc user=testuser password=testpass datasrc=mydatasource;
Typically you should provide user name and password when connecting to a SQL server. I assume you've verified that the ODBC connection was set up correctly (such as testing it with R).
通常，您应该在连接到 SQL 服务器时提供用户名和密码。我假设您已经验证了 ODBC 连接是否正确设置（例如使用 R 对其进行测试）。
Edit from comments: If you're using NT authentication, then follow the instructions here: (from http://support.sas.com/techsup/technote/ts802.pdf)
从评论中编辑：如果您使用的是 NT 身份验证，请按照此处的说明进行操作：（来自http://support.sas.com/techsup/technote/ts802.pdf）
libname odbclib odbc noprompt="dsn=sql_NT;Trusted_Connection=yes"
schema=DBO;
I suspect the old style of just dsn="stuff" doesn't work on newer versions - though I only use OLEDB so I'm not 100% sure.
我怀疑只有 dsn="stuff" 的旧样式在较新版本上不起作用 - 虽然我只使用 OLEDB 所以我不是 100% 确定。
回答by Ornelas
I have tried to connect using both proc sql statements and the libname format. The latter seems more user friendly, here is one example:
我尝试使用 proc sql 语句和 libname 格式进行连接。后者似乎更用户友好，这是一个例子：
LIBNAME testLib ODBC DSN='sqlserver' user=userID pw=xxxxxxxx schema=dbo;
title 'Testing Libname Option';
proc contents data= testLib.mytable;
proc print data=testLib.mytable(obs=10);
UserID and PWD need to come from your ODBC connection setup.
UserID 和 PWD 需要来自您的 ODBC 连接设置。
回答by Prateek sahu
I am a SAS newbie and wasted quite a lot of time trying to create a 'user DSN' . Thankfully, I checked with admin team in my company. One of the SAS admin replied "DSNs are configured on SAS server side " She requested for 'MSSQL database and SAS server name' and created DSN for me.
我是 SAS 新手，在尝试创建“用户 DSN”时浪费了很多时间。值得庆幸的是，我与公司的管理团队进行了核对。其中一位 SAS 管理员回答“DSN 是在 SAS 服务器端配置的”，她请求“MSSQL 数据库和 SAS 服务器名称”并为我创建了 DSN。
Once connection was established i used below query
建立连接后，我使用以下查询
(P.S : I used windows authentication)
（PS：我使用了windows身份验证）
LIBNAME sql odbc = 'xxxxxx' ;
LIBNAME myfolder 'xxxxxxxxxxxxxxxx' ;
proc sql;
create table sql.target_table as select * from myfolder.mydata ;
quit;
