Help using Base SAS procedures

Proc SQL connect to ODBC

Accepted Solution Solved
Reply
Super Contributor
Posts: 426
Accepted Solution

Proc SQL connect to ODBC

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.


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 5,085

Re: Proc SQL connect to ODBC

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.

View solution in original post


All Replies
Super Contributor
Posts: 426

Re: Proc SQL connect to ODBC

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;
Super User
Super User
Posts: 7,408

Re: Proc SQL connect to ODBC

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

Super Contributor
Posts: 426

Re: Proc SQL connect to ODBC

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.

Solution
‎09-25-2015 06:23 AM
Super User
Posts: 5,085

Re: Proc SQL connect to ODBC

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.

Super User
Super User
Posts: 7,408

Re: Proc SQL connect to ODBC

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 399 views
  • 0 likes
  • 3 in conversation