I've installed MySQL connector and added the connection on ODBC Data Source Administrator, I did as well the test: "connection successful".
So I went to import data on data preparation -> Server -> ODBC. After filled the fields I tried to find tables but was sended to me that message:
No one table was returned from the library selected
NOTE: This session is executing on the X64_SR12R2 platform.
NOTE: Additional host information:
X64_SR12R2 WIN 6.3.9600 Server
NOTE: SAS Initialization used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
NOTE: The autoexec file, C:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1
2 The SAS System 12:08 Tuesday, April 16, 2019
1 The SAS System 12:08 Tuesday, April 16, 2019
1
2
/* *********************************************** */
/* Assign the libref for the data in a ODBC table */
/*
2 ! *********************************************** */
LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES
2 ! DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;
NOTE: Libref DB_ODBC was successfully assigned as follows:
Engine: ODBC
Physical Name: DataBase
3 options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;
I found the problem, there is no SAS odbc driver available, so I cannot connect in no one data base.
Thanks for the help
The tables/views contains 32 characters or more? The adm user can access in objects in MYSQL?
The table doesn't contains more than 32 characters.
I didnt understand that question: The adm user can access in objects in MYSQL?
I'm using ODBC with mysql driver connector, is it correct? Or should I use SAS connector to be possible acess my data base through the SAS Visual Analytics?
Please run the following program and let me know if you will get anything in the output from proc contents:
LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;
proc contents data=db_odbc._all_ nods; run;
The query ran successfully but generated warnings. See the Messages tab for more details.
WARNING: No matching members in directory.
That was the answer.
Do you have any tutorial, explaining clearly how I access a data base from SAS VA data preparation? Maybe I did something wrong during my process.
Do you know what is weird? I do not see the SCHEMA= option in your LIBNAME statement. Do you know your database schema name? If so, add it to the LIBNAME statement and try again.
LIBNAME db_odbc ODBC PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES SCHEMA="bi_teste" DATAsrc="DataBase" USER=adm PASSWORD="{sas002}341F943245F55BDC2B048AB10EEBE51E" ;
proc contents data=db_odbc._all_ nods; run;
I executed that command and received the same answer I told you before
Either schema isn't correct or you are using the wrong database. Did you try to connect to that database from a command line outside of SAS?
Yes I did, I'm using Dbeaver to do some queries, everthing is working well and the schema's name is right
Nice!
In this case is MS SQL Server:
Data Source (ODBC Name)
work for me.
Pre-requisites:
GRANT and objects names less or equal than 32 characters.
I don't have MySQL drivers, only MS SQL Server drivers.
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!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.