Connect SAS to a amazon RDS mariadb

edison83 — Mon, 11 May 2020 20:58:35 GMT

Hello guys, i am sorry again for my dumb question!

I am pretty naive in SAS, and now i have a task that might sounds simple for you guys, however it seems an"UFO" for me.

I will start to work with some company data and they sent me a database instance from their AWS database for me

They sent me the Host/IP of the database, user and password

I don`t even know where to start and how to connect with SAS...I heard something about MySQL, odbc, but those terms sounds like UFO for me hahaha

Anyways, just wanted your suggestions on where to get started

What do i need to first, do i have to create a database within SAS?

Thanks guys

Re: Connect SAS to a amazon RDS mariadb

AnandVyas — Tue, 12 May 2020 02:52:56 GMT

Hi @edison83

SAS can connect to different databases using access engines native to that database or generic ones like ODBC and JDBC. First of all you should check if your site has these licensed or not. In order to check the license at your site run the following command

proc setinit; run;

Once you are sure that the required license i.e. SAS/Access to MySQL for MariaDB or ODBC (as in generic) is installed and with the details i.e. host/port/id/pass already shared, you can write a libname statement to make a connection to the database.

SAS/Access to MySQL supports following databases and versions:

Support for MySQL
Starting with SAS 9.4M4 SAS/ACCESS Interface to MySQL supports the following:

Amazon RDS Aurora (MySQL engine version 5.6 or later)
Amazon RDS MariaDB (engine version 10.1 or later)
Amazon RDS MySQL (engine version 5.6 or later)
Azure Database for MySQL (engine version 5.6 or later)
Google Cloud Platform Cloud SQL MySQL (engine version 5.6 or later)
MariaDB 10.1 or later
MemSQL 6.0 or later

source: https://support.sas.com/en/documentation/third-party-software-reference/9-4/support-for-database.html#mySql

Example of libname statement:

MySQL LIBNAME Statement Example
In the following example, the libref MYSQLLIB uses SAS/ACCESS Interface to MySQL to connect to a MySQL database. The SAS/ACCESS connection options are USER=, PASSWORD=, DATABASE=, SERVER=, and PORT=.

libname mysqllib mysql user=myusr1 password=mypwd1 database=mysqldb
server=mysrv1 port=9876;

proc print data=mysqllib.employees;
   where dept='CSR010';
run;

Read more about the different options and syntax at this link:

https://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=n1lyg1d0crcejin1v7zaeteeniuu.htm&docsetVersion=9.4&locale=en#p0ed8b3z2755jkn1001miu4u0r2u

Hope this helps!

topic Re: Connect SAS to a amazon RDS mariadb in ODS and Base Reporting

Connect SAS to a amazon RDS mariadb

Re: Connect SAS to a amazon RDS mariadb