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.
Advertisement
Answer
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;