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.
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.
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;
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
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.
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.
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.