SAS.xpt/STATA.dta, field descriptions, and dbWriteTable
I have a large database (~100MB in 13 relational
files) that made its way to me in two formats. The
files were sent in both SAS xport and STATA, thanks, I
am told, to stat-transfer. The two files, ostensibly,
contain the same data, just in different formats.
My goal: Move these files to MySQl without the help of
SAS or STATA (which I do not have).
The tools I am using are:
1. sasxport.get from Hmisc
2. read.dta from foreign
3. dbWriteTable with the MySQL driver to create and
populate the tables.
The import and table creation went well, though the
SAS file created all text field types in the MySQl
database. (I am not sure if this is a characteristic
of the original data set or a feature of sasxport.)
Importing and writing to MySQL from the STATA file did
just as well (thought taking a bit longer) and
preserved the field types.
However, what neither did (and I am certain this is a
limitation of dbWriteTable) is write the field
comments to the column comments in MySQL. I could see
the column descriptions in R as part of the imported
STATA file. A feature request for dbWriteTable is
these elements are available, perhaps... In any case,
this leads to my question:
Does anyone know of a convenient way to write the
column descriptions from R to the column comments in
MySQL? The object returned by read.dta is a list that
has the data.frame, the row names, the column names,
and the descriptions, all nicely indexed. The column
names, ofe course, are the same ones dbWriteTable used
when it created the MySQL tables. My only thought for
now is looping through these lists and embedding the
needed SQL to write the comments. I am not certain my
programming in either language is up to that though.
Is there a better way?