John's Blog

SAS/ODBC bug when importing from MySQL

So I store a bunch of my dissertation data in a MySQL database running on OSX, storing the different pieces of my data in separate tables, with common user IDs across the tables. Then when I'm in SAS on my windows box, it is trivial to connect to the database with a single line of code.

libname tastedb ODBC datasrc="tastedata" user=XXX password=XXX

Reading in the separate tables from MySQL into unique datasets in SAS is similarly trivial, thanks to the ODBC widget invoked above.

data gene;
set tastedb.genedata;
KEEP id genotype;
run;

data phenotype;
set tastedb.phenotypedata;
KEEP id variable_x variable_y variable_z;
run;

This makes it wicked easy to merge large amounts of data using SQL left joins right in SAS. Here's the code:

PROC SQL FEEDBACK;
CREATE TABLE merged AS
SELECT *, gene.ID as geneID, phenotype.ID as phenotypeID
FROM gene LEFT JOIN phenotype
ON gene.ID=phenotype.ID;
run;
quit;

So once you store your data in MySQL, retrieving lots of data is very easy. More importantly, a little legwork upfront saves you countless hours that would otherwise be spent cutting and pasting in excel to merge datasets for analysis. Now, if you only had a few variables, it's not so bad, but our food frequency questionnaire is hundreds of variables long just by itself.

But there is one little caveat - SAS appears to have a bug where it can get cranky depending on the field type in MySQL. I just spend 45 minutes chasing down an incredibly vague error message in SAS

"ERROR: Invalid sequence of commands for file TASTEDB.foo.DATA"

It turns out that the import from MySQL will fail if you have a text field where all the values are NULL. In one of my tables I had two text fields, Notes1 and Notes2, that were currently empty, in case I wanted to make any comments about that row (subject) in the future. Apparently, SAS doesn't like this. The solution was to add a single comment to the first field and to delete the second.

---