Connect to ODBC and using join with existing SAS dataset

[devsas]

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!

[ChrisBrooks · Accepted solution]

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

[devsas]

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"

;

[Reeza]

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.

[ChrisBrooks]

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