How to query SSMS via SAS EG ODBC connection while using data from WORK?

Question

Is there a way to send a SAS dataset through a proc sql odbc query in SAS EG so that it can be taken in and used by SQL server?

ex)

SAS Data WORK.A contains 3 columns ID, col1, col2. I have a table in Sql Management Studio environment with the same ID column.

I would like to (somehow) do as shown in Figure A below:

Figure 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;

This throws an obvious error in SAS as SSMS doesn't understand what WORK.A is... It's expecting pure SSMS code to be executed.

I have passed macro variables created in SAS through to SQL passthrough to be used in the WHERE statement like in figure B, but that has it's limitations (especially with macro character length) and is not as elegant as passing a whole table.

Figure 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();

Any ideas would be helpful.

Answer 1

Create a temporary table in SQL Server, against which you can perform your later pass through query.

Example:

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;