SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Proc Sql pass through on multiple server

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Proc Sql pass through on multiple server

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


Accepted Solutions
Solution
‎04-01-2018 11:20 PM
PROC Star
Posts: 1,288

Re: Proc Sql pass through on multiple server

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


All Replies
Super User
Posts: 3,860

Re: Proc Sql pass through on multiple server

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.

Super User
Posts: 5,852

Re: Proc Sql pass through on multiple server

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
Solution
‎04-01-2018 11:20 PM
PROC Star
Posts: 1,288

Re: Proc Sql pass through on multiple server

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;
Contributor
Posts: 44

Re: Proc Sql pass through on multiple server

Thanks.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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