Tales of woe from fixed-format (non-delimited) ASCII data distribution

Question

It seems that many/most government surveys in the health sector are distributed in a fixed-format ASCII, often with SAS code provided to create variables out of the mess (e.g. NHAMCS values and formats).

By fixed-format ASCII I mean a file that looks like this:

0101010111239352w734    the cat jumped2432738564375
0101010111239352w734    the cat jumped2432738564375
0101010111239352w734    the cat jumped2432738564375
0101010111239352w734    the cat jumped2432738564375

With a set of instructions or (often non-machine-readable) codebook telling you the character number in each row and the number of characters of width that define each variable.

Anecdotally, I seem to have far more data errors when reading in these files (plus it takes way longer).

Are there documented stories out there of data errors that could be used to make a compelling use case to agencies to switch formats?

I'm thinking of something like the European Spreadsheet Risks Group list, but focused on this specific problem instead of spreadsheets.

Answer 1

I gave a talk many years ago (2008) at the joint AGU/SPD meeting on problems trying to read catalogs, but I don't know that it's directly applicable, as many of the cases were due to manually maintained files that were expected to be read by humans, not machines.

Our community does have a standard for documenting the files in a language-independant way (example), but some of the problems that I ran into was that the documentation was just flat out wrong -- the documentation was written years before, and someone changed the table format without updating the docs.

I did keep some other documentation of the issues that I ran into with trying to read catalogs, but I instead formatted it as a checklist of recommended practices, not as specific 'tales of woe'.

Answer 2

i agree with you that it's preferable when governments provide data in a format that does not require users to purchase proprietary software. but lobbying government statistical divisions is a lot more work than just writing the code ourselves. so i wrote the SAScii package for the R language. now sas-providing government agencies don't have to do anything. :)

click here to read about it.

click here to read my documentation.

click here to get it working on various government surveys, or you could click here to see the actual points in my united states government data scripts that it gets used.

i haven't converted nhamcs yet, but it's on my list. if you need to get bigger data into sqlite or monetdb, check out those folders on my github account for versions of SAScii that import directly into those databases and do not overload RAM on big files.

the tools are out there, you just gotta use them. ;) good luck!