Passthrough DB2 and SQL in same iteration

Q1983 — Sat, 18 Dec 2021 00:06:23 GMT

proc sql noprint; 
connect  to DB2 (&cpplogin);
create table t_escrow as select * from connection to db2
   ( SELECT distinct *
     FROM DX.T_escrow_1
	  WHERE tran_date >= '2019-01-01'
	  and loan_num in ('2211','3123','908766','9876') /*need to replace this line with
	  a new select statement and new passthrough connection.*/
		and UPDATE_CD IN ('A','C')   
  	  order by ln_no,tran_date desc
		 	    WITH UR  
   );
     disconnect from db2;
quit;


proc sql;
        connect to sqlsvr (&dlogin_new.);
        CREATE TABLE loan_lookup AS 
        (       SELECT * FROM connection to sqlsvr 
               (       
                       
			   	select lnbr
                       FROM D_Tran.dbo.DT_DBBT_Lookup
               )
        )
        ;
        disconnect from sqlsvr;
        run;
quit;

I need to integrate loan_lookup into the t_escrow table. It will be filtered based on the loan_lookup query however notice the connections are slightly different. Would I need to run the queries separately then combine them (not preferred) or is there a way to handle this in the same iteration despite two different connection strings

Re: Passthrough DB2 and SQL in same iteration

Tom — Sat, 18 Dec 2021 00:11:37 GMT

I don't understand what the question is. You can definitely be connected to multiple external databases at the same time.

proc sql;
  connect to a;
  connect to b;
  create table c as
   select * 
   from (select * from connection to a (select * from table_in_a)) a
   left join (select * from connection to b (select * from table_in_b)) b
  on a.id = b.id
  ;
quit;

But I have a feeling you are trying to ask a different question.

topic Passthrough DB2 and SQL in same iteration in SAS Programming

Passthrough DB2 and SQL in same iteration

Re: Passthrough DB2 and SQL in same iteration