topic Re: how to join db2 data with local data in SAS Programming

how to join db2 data with local data

cellurl — Wed, 14 Feb 2018 20:59:45 GMT

Hi,

Here is what I want:

libname here './'; run;

proc sql;
   connect to db2     d;
   create table here.tmp as 
        select
           *
        from
           d, here.stuff h
        where
           d.thing=h.thing
     ;

I tried below example with no joy. I hope there is a simple solution from you fantastic people!!!

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002678734.htm

Re: how to join db2 data with local data

Reeza — Wed, 14 Feb 2018 21:03:55 GMT

Can you connect to your DB2 using a libname as well? SQL pass through is the same thing as working entirely on your server so you can't see the local data anymore so that approach won't work.

Another common workaround is to select the list of 'things' required, pass that as a macro variable to filter it first and then join the data on the SAS side after the remaining portion is received.

Re: how to join db2 data with local data

SuryaKiran — Wed, 14 Feb 2018 22:07:24 GMT

Hi,

First thing when your using Explicit pass-through is the query runs entirely in the database and gets you the result to SAS. Explicit pass-through can't be used if tables are in different platform( DB2 table and SAS table "Heterogeneous" ).

If your SAS table is very small and want to join with DB2 table which is very large by avoiding SAS to bring the large DB2 data into SAS environment the efficient way is load the SAS table into DB2 and then you can do in-database queries.

Note: Creating a permanent tables is restricted to most users in many environments. In this case check if you can create a DBMS temp table.

Re: how to join db2 data with local data

kiranv_ — Wed, 14 Feb 2018 22:17:40 GMT

You cannot use connect statement and do the join. Once you use connect statement all the queries are moved to the DB2 server and will not have no clue about SAS tables.

One way to accomplish this is by using implicit pass through which is by doing libname for db2 and then doing join

as shown below. @SuryaKiran has some good suggestions how to do this very efficiently.

libname here './'; 
libname mylib db2 user=user-id password=password datasrc=data-source-name;
proc sql;
 
   create table here.tmp as 
        select
           *
        from
           mylib.d o, here.stuff h
        where
           o.thing=h.thing
     ;

Re: how to join db2 data with local data

cellurl — Thu, 15 Feb 2018 17:01:09 GMT

thanks. Code is always best! thanks Kiranv.