×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SAS code help

SAS code help

SAS code help

(OP)
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.

RE: 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
http://sites.google.com/site/sasbuddy/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close