I'm trying to making a connection to an ODBC server however I'm getting this error message:
ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Data source name not found, and no default driver
specified
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
687 create table test1 as select * from connection to ODBC (
688 select
689 a.*
690 FROM rptingdata.roactreacc a
691 FETCH FIRST 20 ROWS ONLY
692
693 ) ;
NOTE: Statement not executed due to NOEXEC option.
694 disconnect from ODBC;
NOTE: Statement not executed due to NOEXEC option.
695 quit;
NOTE: The SAS System stopped processing this step because of errors.
Here's my code:
proc sql; CONNECT TO ODBC (DSN=SERVER user=U212XXXX password = XXXXXXX ); create table test1 as select * from connection to ODBC ( select a.* FROM rptingdata.roactreacc a FETCH FIRST 20 ROWS ONLY ) ; disconnect from ODBC;
quit;
Any help would be appreciated!
The error message is telling you there is no data source called SERVER defined in your odbc.ini (since you are on Unix) nor do you have a default ODBC driver defined. These need to be added before your connection will work.
Alternatively you could try defining everything in the connection string which will look similar to this:
proc sql;
connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;uid=myusr1;pwd=mypwd1;");
create table Want as
select * from connection to odbc
(SELECT *
FROM [MyDatabase].[MySchema1].[MyTable]
)
;
disconnect from odbc;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: