SAS code help

What I got from your query is that; at first you have created one sas dataset by connecting to ODBC; and now you want to join that dataset with another table in database (might be oracle, DB2).

Here the problem is these are two different entities; one is SAS dataset and another is RDBMS table so if you are trying to use SQL pass thru to join these tables it will not work.

The solution to this problem is; use libname statement to connect to external RDBMS; the code will look like this

libname extdb oracle (dsn=server user=userid password=XXXX)

this code is trying to establish connection to oracle; you can use your database name in this place.

This will create a connection to your DB and will treat the tables in that data database like SAS dataset, then you can use your query with simple proc sql as follows;

proc sql;

create table readm.test2 as

select
test1.sys_id,
proc_cd,
srvc_ct
from
readm.test1
left outer join server.sys_id
on test1.sys_id = server.sys_id
;
quit;

dont forget to unassign the library as follows;

libname extdb clear;

*******************************************************

Another way of doing this to restructure your query as follows;

proc sql;
connect to odbc (dsn=server user=userid password=XXXX)
create table readm.test2 as select * from connection to odbc
(select
server.sys_id,
proc_cd,
srvc_ct
from server.sys_id
) as ser ,
readm.test1

left outer join on test1.sys_id = ser.sys_id
;

quit;


This query will first create a temp dataset called ser and then will join it with test1










sasbuddy