Reputation: 842
Import .dat file from repository using R
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()
)
Upvotes: 1
Views: 1191
Answers (2)
Reputation: 71
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)
Upvotes: 1
Reputation: 263489
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)
Upvotes: 3