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
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;
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.
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;
Thanks.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.