topic Re: Proc Sql pass through on multiple server in SAS Data Management

Proc Sql pass through on multiple server

HabAM — Fri, 30 Mar 2018 20:15:49 GMT

I am trying to join tables in two different servers.

my question is:

1) is it possible?

2) if so, How do I point to both connection ?

proc sql;
Connect to odbc as a(noprompt = "server=server1;DRIVER=SQL Server;Trusted Connection=yes;");
Connect to odbc as b(noprompt = "server=server2;DRIVER=Postgre SQL Server;Trusted Connection=yes;");

Create table ml as
Select * from connection to odbc (????a and b????)
(SELECT m.*
from a.labs.dbo.vw_message_logging m
left join (SELECT DISTINCT e.loinc

FROM elr.elr.vocab_master e
) c on c.loinc=m.loinc
where c.loinc is not null and m.date between 'xxxxx' and 'xxxxxx');
disconnect from odbc ;
quit;
run;

Thank you

Re: Proc Sql pass through on multiple server

SASKiwi — Fri, 30 Mar 2018 22:22:04 GMT

A SAS SQL passthru connection can only go to one database server at a time. If the tables you are joining are not very big then the easiest option is to read both tables into SAS first, then join entirely in SAS. If they are big, then you could consider reading the smaller one into SAS, then loading it into the other database as a temporary table, then do an in-database join.

Re: Proc Sql pass through on multiple server

LinusH — Sat, 31 Mar 2018 10:18:12 GMT

Even though I haven't tried it, but I think it's possible for SQL Server to access PostgreSQL data via ODBC as linked tables. In this way you may be able to perform the join in SQL Server, thus only need to move one table prior to the join.
The other way around might also be possible.
Either way, solving it this way is not a SAS issue since it's about RDBMS functionally.

Re: Proc Sql pass through on multiple server

TomKari — Sun, 01 Apr 2018 16:09:08 GMT

There is another technique that could work for you, depending on volume. You can set up a macro variable with the values you need to join, and then use that to extract the data from your second table. I've mocked up an example below.

Tom

proc sql noprint;
	select name into :NameList separated by '", "'
		from sashelp.class
			where sex="M";
quit;

%let NameList = "&NameList";
%put &NameList;

proc sql noprint;
	create table Test as
		select *
			from sashelp.class
				where name in(&NameList);
quit;

Re: Proc Sql pass through on multiple server

HabAM — Mon, 02 Apr 2018 03:21:28 GMT

Thanks.