Import .dat file from repository using R

Question

I would like to import this dataset into a df. I'm trying to convert this SAS support file to R code using read.fwf approach

Define variables name and lenght as described in the SAS file

vars <- c('SEQN',   'HYK1A',    'HYK1B',    'HYK2A',    'HYK2B',    'HYK3CG',   'HYK3DG',   'HYK6SG',   'HYK8SG',   'HYK3CH',   'HYK3DH',   'HYK6SH',   'HYK8SH',   'HYK3CI',   'HYK3DI',   'HYK6SI',   'HYK8SI',   'HYK3CJ',   'HYK3DJ',   'HYK6SJ',   'HYK8SJ',   'HYK3CK',   'HYK3DK',   'HYK6SK',   'HYK8SK',   'HYK3CL',   'HYK3DL',   'HYK6SL',   'HYK8SL',   'HYK3CM',   'HYK3DM',   'HYK6SM',   'HYK8SM',   'HYK3CN',   'HYK3DN',   'HYK6SN',   'HYK8SN',   'HYK3CO',   'HYK3DO',   'HYK6SO',   'HYK8SO',   'HYK3CP',   'HYK3DP',   'HYK6SP',   'HYK8SP',   'HYK9DG',   'HYK9EG',   'HYK9FG',   'HYK11AG',  'HYK12SG',  'HYK9DH',   'HYK9EH',   'HYK9FH',   'HYK11AH',  'HYK12SH',  'HYK9DI',   'HYK9EI',   'HYK9FI',   'HYK11AI',  'HYK12SI',  'HYK9DJ',   'HYK9EJ',   'HYK9FJ',   'HYK11AJ',  'HYK12SJ',  'HYK9DK',   'HYK9EK',   'HYK9FK',   'HYK11AK',  'HYK12SK',  'HYK9DL',   'HYK9EL',   'HYK9FL',   'HYK11AL',  'HYK12SL',  'HYK9DM',   'HYK9EM',   'HYK9FM',   'HYK11AM',  'HYK12SM',  'HYK9DN',   'HYK9EN',   'HYK9FN',   'HYK11AN',  'HYK12SN',  'HYK9DO',   'HYK9EO',   'HYK9FO',   'HYK11AO',  'HYK12SO')
len <-c(7,  3,  3,  3,  3,  3,  3,  4,  4,  3,  3,  4,  4,  3,  3,  4,  4,  3,  3,  4,  4,  3,  3,  4,  4,  3,  3,  4,  4,  3,  3,  4,  4,  3,  3,  4,  4,  3,  3,  4,  4,  3,  3,  4,  4,  4,  4,  4,  6,  4,  4,  4,  4,  6,  4,  4,  4,  4,  6,  4,  4,  4,  4,  6,  4,  4,  4,  4,  6,  4,  4,  4,  4,  6,  4,  4,  4,  4,  6,  4,  4,  4,  4,  6,  4,  4,  4,  4,  6,  4)

retrieve DF from the web

df <- read.fwf(".../you.dat",
                    widths = len, header = FALSE, n=10, stringsAsFactors = TRUE)
names(df) <- vars

Visualize the DF

head(df)

Honestly, I don't trust this DF. I'm getting too many NAs

Update after @42- illuminating answer. Faster way

I improved my code easily using SAScii library and it works. However, I found something faster with lower system expenses here.

library(readr)
library(data.table)

#Parse SAS file
vars <- parse.SAScii(".../you.sas")

setDT(vars) #convert to data.table

#read to data frame
huge.df <- read_fwf(".../you.dat", 
                     fwf_widths(dput(vars[,width]),
                                col_names=(dput(vars[,varname]))),
                     progress = interactive()
)

This looks like a question more for [opendata.se], though i'm not that familiar with its topic area to know this specific kind of question is okay there. You haven't really phrased it in a way that's on topic here, though (as a programming question), it's more just a collection of thoughts. Come back with more concrete programming questions if you have them, or ask over there. — Joe, May 11 '18 at 19:29
I disagree. It's a very well documented data access question that is very R-specific and has a reproducible example (unlike many we see here.) It's major stumbling block to answering was failing to point out a link to the documentation files which took a bit of searching to track down because the CDC's website was a bit incomplete in spots. I would have set strings as factors to FALSE myself. — IRTFM, May 11 '18 at 21:06

IRTFM · Accepted Answer · 2018-05-11T21:15:30.823

A few years ago I also tried doing that and eventually cobbled together a method, but in the meantime, @AnthonyDamico wrote the SAScii-package and all these efforts are now unnecessary. Thank you, Anthony, for all your excellent efforts at making public use data available to R users. I suspect you've saved the Kaiser Foundation hundreds of thousands of dollars over the course of your employment and consulting career.

library(SAScii)
youth.tf <- tempfile()
daturl <- "https://wwwn.cdc.gov/nchs/data/nhanes3/2a/youthk.dat"
code_url ="https://wwwn.cdc.gov/nchs/data/nhanes3/2a/youthk.sas"
Sas_code <- url(code_url)
writeLines ( readLines(Sas_code) , con = youth.tf )
youth.fwf.parameters <- parse.SAScii( youth.tf , beginline = 5 )
str( youth.fwf.parameters )
#-----
'data.frame':   90 obs. of  4 variables:
 $ varname: chr  "SEQN" "HYK1A" "HYK1B" "HYK2A" ...
 $ width  : num  5 1 1 2 2 2 2 4 4 2 ...
 $ char   : logi  FALSE FALSE FALSE FALSE FALSE FALSE ...
 $ divisor: num  1 1 1 1 1 1 1 1 1 1 ...
#------

daturl <- "https://wwwn.cdc.gov/nchs/data/nhanes3/2a/youthk.dat"
in.youth <- read.fwf(daturl, widths=youth.fwf.parameters$width,
                       col.names= youth.fwf.parameters$varname)

I'm afraid there are quite a few NA's and appears your code was mostly successful.

str(in.youth)
'data.frame':   13944 obs. of  90 variables:
 $ SEQN   : int  7 12 13 14 16 20 21 23 26 27 ...
 $ HYK1A  : int  1 1 2 2 2 2 1 2 2 2 ...
 $ HYK1B  : int  2 2 2 2 2 2 2 2 2 2 ...
 $ HYK2A  : int  1 1 NA NA NA NA 1 NA NA NA ...
 $ HYK2B  : int  NA NA NA NA NA NA NA NA NA NA ...
 $ HYK3CG : int  1 3 NA NA NA NA 4 NA NA NA ...
 $ HYK3DG : int  2 3 NA NA NA NA 2 NA NA NA ...
 $ HYK6SG : int  30 30 NA NA NA NA 30 NA NA NA ...
 $ HYK8SG : int  24 1 NA NA NA NA 24 NA NA NA ...
 $ HYK3CH : int  NA NA NA NA NA NA NA NA NA NA ...
 $ HYK3DH : int  NA NA NA NA NA NA NA NA NA NA ...
 $ HYK6SH : int  NA NA NA NA NA NA NA NA NA NA ...
 $ HYK8SH : int  NA NA NA NA NA NA NA NA NA NA ...
 $ HYK3CI : int  NA NA NA NA NA NA NA NA NA NA ...
 $ HYK3DI : int  NA NA NA NA NA NA NA NA NA NA ...
 $ HYK6SI : int  NA NA NA NA NA NA NA NA NA NA ...
.... snipped the rest of the 90 variables

I only see these as having possibly invalid data and they appear to have not been loaded correctly:

 HYK11AG: Factor w/ 179 levels "      ","003.9 ",
HYK11AH: Factor w/ 108 levels "      ","011.9 ",
HYK11AK: Factor w/ 12 levels "      ","079.9 ",

These appear to be ICD-9-CM codes that should have been read into R with character format, but for which there were not the proper $ entries in the INPUT statements in the CDC SAS code. You can get a full set of diagnostics with the "read.SAScii"-function:

in2 <- read.SAScii( daturl, code_url)

You can check the counts of non-NA values against the values reported in the codebook at: https://wwwn.cdc.gov/nchs/data/nhanes3/1a/YOUTH-acc.pdf

This is a partial screenshot of page 210 of that codebook and shows that there should be all NA's for the three "HYK__" variables on that page:

Note added for amusement. This is actually now a counter-example of sorts to this R fortune. There are quite a few items in that package referencing SAS (often in a less than totally favorable light) and it took me 7 or 8 tries before the one I was looking for was brought up:

fortunes::fortune("SAS")

Jim Gustafsson: I would like to put my SAS-code into R. Could I do that,
if yes, how?
Frank Harrell: Just reverse the procedure you use when you put R code into
SAS. ;)
   -- Jim Gustafsson and Frank Harrell
      R-help (February 2004)

score 1 · Answer 2 · answered Dec 25 '18 at 06:18

1

With the SAScii package, this could be easily executed as a one-liner following the general form: read.SAScii (dataset, sascode, zipped = F). For example:

mydata <- read.SAScii (".../you.dat", ".../you.sas", zipped = F)

answered Dec 25 '18 at 06:18

Ter

51
4

Import .dat file from repository using R

2 Answers2

Linked