Proc SQL connect to ODBC

[Babloo]

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.

[Babloo]

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;

[RW9]

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

[Babloo]

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.

[Astounding · Accepted solution]

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.

[RW9]

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;