DATA Step, Macro, Functions and more

Connect to ODBC and using join with existing SAS dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

Connect to ODBC and using join with existing SAS dataset

Good Morning everyone! So, Im trying to connect with SQL server using connect to ODBC and it works well as long as im quering the data from a table in SQL server. But, at times, I need to join the table im quering with a local SAS dataset. I use the following code and it gives me error. Ofcourse there is an option of getting the whole table from sql sever first and then do the join, but the tables im working with are very big tables in sql server and also the data changes in them constantly, so bringing them first to SAS will be a pain.

In the code below, enbmast is the big table in sql server. The code below works fine if I dont join it with members table.

 

proc sql;

connect to odbc as AX (dsn="EIM");

create table test_ as

select *

from connection to AX (

select a.acctno, SUM(a.trnamt) AS TRNAMT1, b.memID 

from enbmast a join work.members b on a.acctno =b.acctno GROUP BY ACCTNO

 

);

quit;

 

Thanks so much in advance!


Accepted Solutions
Solution
‎09-18-2017 11:47 AM
Valued Guide
Posts: 576

Re: Connect to ODBC and using join with existing SAS dataset

You're using Pass-through so you won't be able to join with SAS data sets as all the "action" takes place in the DB. You'll have to declare a Libname using the ODBC engine as here -> http://support.sas.com/kb/52/777.html and use "normal" Proc SQL

View solution in original post


All Replies
Solution
‎09-18-2017 11:47 AM
Valued Guide
Posts: 576

Re: Connect to ODBC and using join with existing SAS dataset

You're using Pass-through so you won't be able to join with SAS data sets as all the "action" takes place in the DB. You'll have to declare a Libname using the ODBC engine as here -> http://support.sas.com/kb/52/777.html and use "normal" Proc SQL

Frequent Contributor
Posts: 121

Re: Connect to ODBC and using join with existing SAS dataset

Posted in reply to ChrisBrooks

Thanks so much! I used the code in the link you provided replacing relevant names for database and server names. It seems to work, except that i was not sure of what to put for schema and removing that seems to work. But, just want to confirm if schema option is optional?

 

libname mydata odbc complete="driver=SQL Server;

database=DHS;

server=EIMSQLCLS2X\ODE"

;

 

Super User
Posts: 23,357

Re: Connect to ODBC and using join with existing SAS dataset

The schema option is optional but I've found that most places need it. If it isn't explicitly set that you're aware of, try setting it to dbo and see what happens.

Valued Guide
Posts: 576

Re: Connect to ODBC and using join with existing SAS dataset

Strictly speaking it is optional - if you don't specify a schema you'll connect to the default schema.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 312 views
  • 2 likes
  • 3 in conversation