SAS code help

[mayhem11]

My first piece of code ends with disconnect from odbc; quit;
and it will write the data I selected from a server and write the final table into my personal libname

then i have to take the sys_id from this table I created and saved to my libname and then do a new odbc to the server using the sys_id to connect to the server_sys_id along with a few other columns in the server and make another table that is then saved to my libname

The sys_id in the first query pulls distinct sys_id's that have certain other filters so I end up with a 233k row table of the millions of sys_id's in the main server database. Then my goal is take the 233k distinct sys_id's and reconnect to the server via odbc to perform some other functions that I cannot perform in the first query.

I was attempting the following:

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

i also tried using
on readm.test1.sys_id

the from statement as
from
libname readm.test1
left outer join

I just cannot figure out how to do my odbc to the server as well as use the table in my libname and connect the two to create what I need.

 

[sasbuddy]

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

http://sites.google.com/site/sasbuddy/