BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HabAM
Quartz | Level 8

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

HabAM
1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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;

View solution in original post

4 REPLIES 4
SASKiwi
PROC Star

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.

LinusH
Tourmaline | Level 20
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.
Data never sleeps
TomKari
Onyx | Level 15

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;
HabAM
Quartz | Level 8

Thanks.

HabAM

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 2426 views
  • 3 likes
  • 4 in conversation