如何在使用WORK中的数据时通过SAS EG ODBC连接查询SSMS

Question

有没有办法通过SAS EG中的proc-sql-odbc查询发送SAS数据集，以便sql server可以接收和使用它？

ex(

SAS数据WORK.A包含3列ID，col1，col2。我在SqlManagementStudio环境中有一个具有相同ID列的表。

我想(以某种方式(如下图A所示：

图A(

proc sql;
Connect to odbc("driver=SQL Server; database=SSMSDatabase; Server=SSMSServer");
create table WORK.B as                                                             
select * from connection to odbc                                                      
(
Select t1.*, t2.* 
from SSMSTable1 t1 
INNER JOIN WORK.A t2 ON t1.ID = t2.ID
);
disconnect from odbc;
quit;

这在SAS中引发了一个明显的错误，因为SSMS不理解WORK.A是什么……它期望执行纯SSMS代码。

我已经将在SAS中创建的宏变量传递到SQL传递，以便在WHERE语句中使用，如图B所示，但这有其局限性(尤其是宏字符长度(，并且不如传递整个表那么优雅。

图B(

proc sql; 
select cat("'",trim(ID),"'") 
into :list1 separated by "," 
from WORK.A; 
quit;
%macro ODBCRun();
proc sql;
Connect to odbc("driver=SQL Server; database=SSMSDatabase; Server=SSMSServer");
create table WORK.B as                                                             
select * from connection to odbc                                                      
(
Select *
from SSMSTable1 
WHERE ID IN (&list1.)
);
disconnect from odbc;
quit;
%mend;
%ODBCRun();

任何想法都会有所帮助。

Answer 1

在SQL Server中创建一个临时表，您可以对该表执行稍后的传递查询。

示例：

libname SS odbc 
dbmstemp=yes
noprompt="driver=SQL Server; database=SSMSDatabase; Server=SSMSServer"
;
* upload SAS data into SQL Server;
proc delete data=ss.'#idlist'n;
run;
data ss.'#idlist'n;
set WORK.A;
run;
* Use uploaded data in pass through query;
proc sql;
connect using SS as REMOTE; * reuses connection libref made;
create table WORK.B as                                                             
select * from connection to REMOTE
(
select t1.*, t2.* 
from
SSMSTable1 t1 
INNER JOIN
#idlist t2
ON 
t1.ID = t2.ID
);
disconnect from REMOTE;
quit;