Search code examples
rimportcross-platformstatafixed-width

Is there a simple way to use a Stata .do script to import fixed width data into R?

I have a Stata .do script file that is used to import data from a fixed width TXT file. Here is what the .do file looks like:

#delimit ;

**************************************************************************
   Label           : CDS 2014 ID Map
   Rows            : 4353
   Columns         : 7
   ASCII File Date : December 11, 2017
*************************************************************************;


infix 
      CHLDID14        1 - 5         CHLDSN14        6 - 7         PCGID14         8 - 12   
      PCGSN14        13 - 14        CDSHID14       15 - 18        CHLDINST14     19 - 20   
      PCGINST14      21 - 22   
using [path]\IDMAP14.txt, clear 
;
label variable  CHLDID14     "CHILD 2013 PSID FAMILY IW (ID) NUMBER" ;           
label variable  CHLDSN14     "CHILD 2013 INDIVIDUAL SEQUENCE NUMBER" ;           
label variable  PCGID14      "PCG 2013 PSID FAMILY IW (ID) NUMBER" ;             
label variable  PCGSN14      "PCG 2013 INDIVIDUAL SEQUENCE NUMBER" ;             
label variable  CDSHID14     "CDS 2014 HOUSEHOLD INTERVIEW NUMBER" ;             
label variable  CHLDINST14   "CDS 2014 HH ROSTER CHILD SEQUENCE NUM" ;           
label variable  PCGINST14    "CDS 2014 HH ROSTER PCG SEQUENCE NUM" ;

Is there a quick way to use this .do file to import the data into R automatically? Or do I have to adapt the script manually, using the column ranges?

I'm asking because I only have access to R (not Stata), but the Stata .do file seems to be the easiest shortcut to import the data properly into R.

Thanks!

Link to files: Fixed-width text file and Stata .do script

Solution

  • Here's a stab, but since we don't have a file with which to verify, you're on your own for that. There are likely many assumptions I've made with this format that need to verified, namely:

    • we find column definitions between a line with a literal infix and literal using
    • each column definition is precisely columnname from hyphen to, with spaces (even if a single character, this would be somename 5 - 5)
    • the filename immediately follows the literal using; a trailing comma might be followed by clear or other non-comma characters, not part of the filename
    do2fwf <- function(txt) {

  infix <- grep("^infix\\s*", txt) 
  if (length(infix) != 1L) stop("need exactly one 'infix' line")
  using <- grep("^\\s*using\\b", txt)
  if (length(using) != 1L) stop("need exactly one 'using' line")
  if (using < infix) stop("'infix' must occur before 'using'")

  hdrtxt <- txt[ (infix+1):(using-1) ]
  # "      CHLDID14        1 - 5         CHLDSN14        6 - 7         PCGID14         8 - 12   "

  re <- gregexpr("\\S+", hdrtxt)
  m <- regmatches(hdrtxt, re)
  # [[1]]
  #  [1] "CHLDID14" "1"        "-"        "5"        "CHLDSN14" "6"        "-"        "7"        "PCGID14"  "8"       
  # [11] "-"        "12"      

  if (!all(lengths(m) %% 4 == 0))
    stop("not all variables are the right format of 'name i - j'")
  if (any(lengths(m) == 0)) {
    warning("found empty lines, confusing")
    m <- Filter(length, m)
  }

  # need to convert 4x lists into 1x lists
  m2 <- do.call("c", mapply(split, m, lapply(lengths(m), function(a) (1:a-1) %/% 4)))

  nms <- sapply(m2, `[[`, 1)

  froms <- as.integer(sapply(m2, `[[`, 2))
  tos <- as.integer(sapply(m2, `[[`, 4))
  widths <- tos - froms + 1

  filename <- gsub("^\\s*using\\s*", "", txt[using])
  # this works here, but I don't know if it is generic and rule-following
  filename <- gsub("\\s*,[^,]*$", "", filename)

  list(filename = filename, names = nms, widths = widths)
  # x <- read.fwf(filename, widths=widths, ...) # header=FALSE???
  # colnames(x) <- names
}

    If you use the data at the bottom (which should really just be txt <- readLines("somefile.do"), you'll get this:

    do2fwf(txt)
# $filename
# [1] "[path]\\IDMAP14.txt"
# $names
#            0            1            2            0            1            2            0 
#   "CHLDID14"   "CHLDSN14"    "PCGID14"    "PCGSN14"   "CDSHID14" "CHLDINST14"  "PCGINST14" 
# $widths
# [1] 5 2 5 2 4 2 2

    Which you can use (per the comments) on your own. I don't know about a header row or other arguments you might need for read.fwf. Good luck!

    Text:

    txt <- readLines(textConnection('**************************************************************************
   Label           : CDS 2014 ID Map
   Rows            : 4353
   Columns         : 7
   ASCII File Date : December 11, 2017
*************************************************************************;


infix 
      CHLDID14        1 - 5         CHLDSN14        6 - 7         PCGID14         8 - 12   
      PCGSN14        13 - 14        CDSHID14       15 - 18        CHLDINST14     19 - 20   
      PCGINST14      21 - 22   
using [path]\\IDMAP14.txt, clear 
;
label variable  CHLDID14     "CHILD 2013 PSID FAMILY IW (ID) NUMBER" ;           
label variable  CHLDSN14     "CHILD 2013 INDIVIDUAL SEQUENCE NUMBER" ;           
label variable  PCGID14      "PCG 2013 PSID FAMILY IW (ID) NUMBER" ;             
label variable  PCGSN14      "PCG 2013 INDIVIDUAL SEQUENCE NUMBER" ;             
label variable  CDSHID14     "CDS 2014 HOUSEHOLD INTERVIEW NUMBER" ;             
label variable  CHLDINST14   "CDS 2014 HH ROSTER CHILD SEQUENCE NUM" ;           
label variable  PCGINST14    "CDS 2014 HH ROSTER PCG SEQUENCE NUM" ;             '))