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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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
     ;

 

View solution in original post

4 REPLIES 4
Reeza
Super User

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.

SuryaKiran
Meteorite | Level 14

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.

Thanks,
Suryakiran
kiranv_
Rhodochrosite | Level 12

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
     ;

 

cellurl
Quartz | Level 8
thanks. Code is always best! thanks Kiranv.

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
  • 4 replies
  • 3157 views
  • 1 like
  • 4 in conversation