BookmarkSubscribeRSS Feed
buechler66
Barite | Level 11

Hi. Is it possible to merge two tables that reside on two seperate databases.  In my case table ivas.ods_iv_recon_selected_mp sits on database ibscrprd, but table imapsscr.ods_bi_recon_selected_mp resides on database ivasprd.  

 

The query below doesn't work obviously because I have only a Connect To statement for the ibscrprd database. Do you know if it's possible to connect to two databases at once using SQL PassThru? And if so, how I might alter my coding attempt below to do so?

 

Any help would be greatly appreciated. Thanks.

 

proc sql;
connect to oracle as db (user="me" password="mypw" path="ibscrprd");
create table myTable as 
select * from connection to db
(select *,
"A" as source,
"-" as RuleFlag,
"-" as rule_nm length = 58,
actual_dlvry_date as ad_dt,
"-" as rule_order
from ivas.ods_iv_recon_selected_mp
where imb_dlvry_zip_5=&ZIP5_QUOTED
union all corresponding
select *,
"B" as source,
"-" as RuleFlag,
"-" as rule_nm length = 58,
actual_dlvry_date as ad_dt,
"-" as rule_order
from imapsscr.ods_bi_recon_selected_mp
where imb_dlvry_zip_5=&ZIP5_QUOTED;
quit; ); disconnect from db; quit;
10 REPLIES 10
SASKiwi
PROC Star

As long as the databases are on the same server it should be possible. Is this the case? I know how to do it in SQL Server by establishing a connection at the server level and then including the database name on the front of the schema and table names.

 

I'm sure something similar would work for Oracle but I can't research it right now. Maybe someone else will know. 

buechler66
Barite | Level 11

Yes, they are both on the same server.

LinusH
Tourmaline | Level 20

Like @SASKiwi says, this will be Oracle specific syntax within your pass-through block of code.

So research Oracle resources, or get some help from your Oracle DBA/developers.

Data never sleeps
SASKiwi
PROC Star

I think your code should look something like this example. Suggest you run it past your Oracle DBA though - he should know the right syntax

 

proc sql;
connect to oracle as db (user="me" password="mypw" path="ibscrprd");
create table myTable as 
select * from connection to db
(select * from table_a x,table_b@OtherDatabase y where X.row_id = Y.row_id
); 
disconnect from db;
quit;
buechler66
Barite | Level 11
oh a DB Link. Unfortunately we are not allowed to have a DB Link between these two databases. 😞
Quentin
Super User

@SASKiwi could you show an example of how to connect to to SQL server from SAS at the server level instead of the database name? (i.e. what would go in the odbc.ini file, and how an explicit pass-through query would look)? 

 

Just the other day I was wondering if this is possible.   I work on one server where we have multiple databases, and it would be convenient to thave a server-level connection rather than update the OBDC.ini every time someone creates a new database.

 

 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SASKiwi
PROC Star

Sure @Quentin- this example assumes running on Windows but doing the same thing on Unix should be similar:

 

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;");
  create table Want  as 
  select * from connection to odbc
  (SELECT *
   FROM [MyDatabase1].[MySchema1].[Table1] as A
   INNER JOIN [MyDatabase2].[MySchema2].[Table2] as B
   ON A.[Key] = B.[Key]
   )
  ;
  disconnect from odbc;
quit;

 

Quentin
Super User

Thanks @SASKiwi.  Sorry for being dense, but with that connection string (noprompt="...") do you pass enough information that you completely avoid the need to pre-define an ODBC data source (in ODBC administrator on Windows or odbc.ini on linux)? 

 

Okay re-reading the docs, it says NOPROMPT "specifies connection options for your data source or database."  So that means with NOPROMPT I should be able to pass all the information needed to connect to a database on a server, without first defining it as an ODBC data source?  [guess that's the same question twice]

 

So the next time someone says "Please pull some data from database X on server Y using account ReaderZ and PasswordZ " I should be able to do it myself from our SAS server, without asking the admin to define a new ODBC connection for me?  (Assuming I don't need to register the database in metadata)  [Same question third time perhaps, but I'm excited ]

 

That would make me sooo happy.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SASKiwi
PROC Star

@Quentin - Does your SAS server run on Windows or Unix? If on Windows then the NOPROMPT completely defines your SQL Server connection and nothing needs to be defined elsewhere. If you are on Unix then I think you are stuck with using ODBC.INI. Try a query like the one I posted anyway. I suspect it may not matter what database/schema you connect to initially as long as you use the 3-stage table strings to reference tables in other SQL Server databases.

Quentin
Super User
Thanks. Will play with it a bit. Our SAS server is Linux. But I have Windows PC SAS too. So will start by trying to get it to work on Windows, then will move on to Linux.  --Q. 
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 5480 views
  • 3 likes
  • 4 in conversation