BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Babloo
Rhodochrosite | Level 12

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Babloo
Rhodochrosite | Level 12

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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
PROC Star

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
Diamond | Level 26 RW9
Diamond | Level 26

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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