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
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 ;
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.
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.
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 ;
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.
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.