NHANEStoMySQL

The path from SAS transport files to CODAP

The NHANES site lets you download SAS transport files (.xpt). But for our system to use them, we need them in MySQL (or some other database that we will access from CODAP/JS).

SAS transport to CSV

Do this is RStudio. Here is example code:

open(tidyverse)
library(data.table)

bp <- read_xpt("BPX_I.xpt")
fwrite(bp, file="bp.csv")

CSV to MySQL

Using Sequel Pro, choose File\Import and select the .csv. Check that the column headings are correct; the most common mistake for me has been to fail to indicate that this is going to be a new table.

Additional munging

For the data to appear in the NHANES plugin, you also need to do the following in MySQL (probably in Sequel Pro).

• Make sure whatever (new) variables you want to appear in the interface are identified in the varlist table.
• Be sure that any values that need translating are listed (keyed by the internal variable name) in the decoder table.

Prepping the MySQL database

We often need to compute a new column in the MySQL so that we don't have to do it in JS. In NHANES, a good example is in sexual behavior, where they have two different items representing the same question, one asked of males, one of females, e.g., the number of opposite-sex partners. In that case, we might want a single column that collapses the two.

First we need to know the names of the relevant attributes; these are from the CDC at https://wwwn.cdc.gov/Nchs/Nhanes/2015-2016/SXQ_I.htm:

SXD101 - # male sex partners/lifetime (F)
SXD171 - # female sex partners/lifetime (M)
SXQ130 - # female sex partners/lifetime (F)
SXQ410 - # men anal/oral sex partners/lifetime(M)

Then we check the table. What's in the cell when it's the wrong sex? Looks like nothing, but maybe it's an empty string. We'll use UPDATE and IF, like this:

UPDATE sexbeh SET SXPOLIFE = IF(SXD101 IS NULL or SXD101 = '', SXD171, SXD101)
UPDATE sexbeh SET SXPSLIFE = IF(SXQ410 IS NULL or SXQ410 = '', SXQ130, SXQ410)