topic Re: Proc SQL connect to ODBC in SAS Procedures

Proc SQL connect to ODBC

Babloo — Tue, 22 Sep 2015 10:50:55 GMT

I'm trying to create table via oracle table. When I tried to run the code below,

%include "/usr/sas/tir/sa_connect.txt";
proc sql;
create table mibipads select * from connection to odbc 
(select bipad,title from mas.bipad_master); 
disconnect from odbc; 
quit;

I got error as,

ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Data source name not found, and no default driver
specified.

sa_connect.txt file having the details to connect oracle from SAS and I it don't seem issue with this .txt. Can someone guide where I need to tweak the code to run without any errors.

Re: Proc SQL connect to ODBC

Babloo — Tue, 22 Sep 2015 10:52:51 GMT

I missed the keyword in my previous post. Code is like,

%include "/usr/sas/tir/sa_connect.txt";
proc sql;
create table mibipads as select * from connection to odbc 
(select bipad,title from mas.bipad_master); 
disconnect from odbc; 
quit;

Re: Proc SQL connect to ODBC

RW9 — Tue, 22 Sep 2015 10:56:26 GMT

You are missing the connection part, the syntax is something like:

proc sql;

connect to odbc (user="ABC",password=...);

select * from connection to odbc (select * from abc);

disconnect from odbc;

quit;

See here for an Oracle version, you will need something similar for your database.

https://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113595.htm

Re: Proc SQL connect to ODBC

Babloo — Tue, 22 Sep 2015 11:07:07 GMT

My connection part in the .txt file and it has,

proc sql; connect to odbc (user=*** password=****_proddb dsn='******');

Note: I put a '*' to hide the confidential information in the public forum.

Re: Proc SQL connect to ODBC

Astounding — Tue, 22 Sep 2015 11:21:18 GMT

Your %included file already contains PROC SQL;

So get rid of the second PROC SQL statement after the %include. That begins a separate SAS step that does not have a connection.

Re: Proc SQL connect to ODBC

RW9 — Tue, 22 Sep 2015 11:59:39 GMT

Why do you have the connection string in a text file? If so then you only need to put the connect part, and include at the place the included text should appear:

sa_connect.txt:

connect to odbc (user=*** password=****_proddb dsn='******');

proc sql;

%include "/usr/sas/tir/sa_connect.txt";

select ...;

disconnect...;

quit;