Blogs

Blogs

Using PROC SQL to get the schema of a MySQL database

5
By

It's Friday, and on SAS Voices they are posting fun stuff about dogs who work at SAS. I'm posting about PROC SQL and MySQL. You tell me - which of us knows how to ring in the weekend with style?

I've been working with MySQL data sources lately, and SAS/ACCESS to MySQL makes it easy to do my work in SAS. I can assign a SAS library and then work with the tables and columns almost exactly as if they lived in native SAS data sets. The database columns are automatically visible to my SAS program with SAS-style attributes, such as SAS formats and lengths.

But when I get started with a new database that I'm not familiar with, I'd like to know how MySQL sees that data, including the database-centric attributes. That information is available in the information_schema structure within the MySQL instance. (Note: other databases support the information_schema, so this program can go beyond just MySQL.)

/* must be run where SAS/ACCESS to MySQL available */
%let database=dbname; /* name of the MySQL database */
%let server = mysql.host.domain.com; /* your MySQL node */
 
proc sql;
  connect to mysql as source 
    (
    DATABASE=&database 
    SERVER=&server 
    PORT=3306 /* default port, change if needed */
    USER=your_user_id
    PASSWORD=secretPW
    );
  create table work.schematables as 
    select * from connection to source 
    (select * from 
      information_schema.tables 
    where table_schema="&database");
 
  create table work.schemacolumns as 
    select * from connection to source 
    (select * from 
      information_schema.columns 
    where table_schema="&database");
  disconnect from source;
 
quit;

After plugging my database information and credentials into the above program, and then running it, I receive two output tables. One table (WORK.SCHEMATABLES) shows me all of the database table attributes, such as the table row count, create/update times, index length, and more. I find the other table (WORK.SCHEMACOLUMNS) to be more interesting. It shows me the column names, intrinsic type (bigint, varchar, datetime, and so on), length, whether it's NULLible, and more.

Oh, and if you're a bit queasy about putting your database credentials into a SAS program like this, check out these five strategies to eliminate passwords from your SAS programs.

Tags MySQL sas/access
Share

About Author

Chris Hemedinger

Director, SAS User Engagement

+Chris Hemedinger is the Director of SAS User Engagement, which includes our SAS Communities and SAS User Groups. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies. 

5 Comments

  1. Peter on

    so, on MySQL it is information_schema.
    On Teradata there is DBC which seems to contain all the metadata about databases, tables, users, user-functions (adding up to hundreds of information tables) but the one most closely corresponding to information_schema.columns is dbc.columnStats.
    Can we assemble the corresponding metadata for more of the popular databases, or should we find this among the documentation for SAS/Access to RDBMS?

    • Chris Hemedinger
      Chris Hemedinger on

      Peter,

      I think that the database documentation from the vendor is probably the best source for learning the DBMS-specific system tables. The SAS/ACCESS documentation might feature a few hints, including some examples, but since PROC SQL CONNECT allows you to issue any command/query that the database can understand, the database documentation is probably the most comprehensive source for what you can do.

  2. Abe John on

    Hi Chris,
    I am new to importing data from SQL server 2008 to work in SAS. what is the easy way to open data from SQL server 2008 in SAS. thank you for your help.

    • Chris Hemedinger
      Chris Hemedinger on

      Abe,

      There are a number of ways, depending on your software configuration. Typically you would need SAS/ACCESS to OLE DB or SAS/ACCESS to ODBC to allow SAS to connect to your database, and then pull the data into your SAS session (via LIBNAME statement).

      If you have SAS Enterprise Guide, you can use File->Open->From OLE DB to read the SQL Server data via the EG client; while that works for smaller tables, it's not the most efficient method. The direct connection from SAS to SQL Server is a better option for larger data and repeated, production access.

Back to Top