How to open/import database information containing a .dat file and a .des file?

[Miles Like]

First off, I apologize for having virtually no knowledge or experience with statistics or databases, which is why I've come here. At the moment, I don't know of a better solution than to simply seek help on these forums. As a heads up, forgive me if I use terminology wrong. I might incorrectly use words like "extension", "schema", or "format", among others. At this point I only have a general idea of what these words mean, but they are not well-defined.

Fortunately, the problem is simple: I want to do some analysis on North Carolina's inmate population. NC has a public database that you can search to find information about convicted offenders. Even better, if you want to analyze the data they provide all the public information from their database in downloadable files, and you can access them here:

http://webapps6.doc.state.nc.us/opi/...do?method=view

At this point it seems like my job is going to be easy enough. That is, until I actually download and unzip the folder. The two files inside the folder seem be based on some primitive and anachronistic database structure. If I open the .dat file in notepad, it's just a complete mess of information with no headers. All the header and presumably structural instructions is contained in the .des file.

I have no idea how to import this information into a database program or convert it into a format that's readable. I thought Stat/Transfer would be able to solve the problem, but unfortunately, even Stat/Transfer doesn't seem to recognize a database format (?) that has .dat file for data, and a .des file for schema (or whatever it is).

What I want to do is simply import this information into a database software (like Excel?) so that it's organized and I can coherently sort through the information. If you know of a way Stat/Transfer can do it, then please let me know. If Stat/Transfer can't do it, then perhaps one of you can direct me to software that can? Or maybe the solution requires something else entirely.

Thanks for any information you can provide.

[Carole J. Wilson]

I'd suggest using

type "filename.dat" , showtabs lines(15)
type "filename.des" ,  showtabs lines(15)

Then copy and paste the the results and post here between the [CODE] [/CODE] delimiters

[Robert Picard]

I had a peek and downloaded the smallest archive (Warrant Issued (OFNT9BE1)). Here's the content of "OFNT9BE1.des":

CMDORNUM      OFFENDER NC DOC ID NUMBER          CHAR      1       7     
WHPREFIX      WARRANT COMMITMENT PREFIX          CHAR      8       2     
WHSENCMP      SENTENCE COMPONENT NUMBER          CHAR      10      3     
WHEXSTAT      WARRANT EXTRADITION STATUS CD      CHAR      13      30    
WHSTACOD      WARRANT STATUS CODE                CHAR      43      30    
DTOFUPDT      DATE OF LAST UPDATE                DATE      73      10    
TMOFUPDT      TIME OF LAST UPDATE                TIME      83      8

and the first few lines of "OFNT9BE1.dat"

000000805001NON EXTRADITABLE              ARRESTED                      2000-03-2308:36:39
000002001001NON EXTRADITABLE              ARRESTED                      1995-12-0415:29:55
0000020BA001NON EXTRADITABLE              ARRESTED                      1995-12-1215:13:04
000002807001NON EXTRADITABLE              ARRESTED                      2000-10-0908:03:41
0000028BA001EXTRADITE                     ARRESTED                      1997-01-2716:54:27
0000035BA001NON EXTRADITABLE              ARRESTED                      1996-10-1513:13:47
000004704001NON EXTRADITABLE              ARRESTED                      2004-09-2310:41:56
000005803001NON EXTRADITABLE              WITHDRWN                      2000-01-3116:25:38
000005803001NON EXTRADITABLE              ARRESTED                      1998-12-2208:59:49
000007405001CLEAR NCIC                    WITHDRWN                      1999-03-1112:58:54

If all the other datasets are like this, you should be happy. This is very straightforward fixed format data that can be read using infix. You can input the whole dataset using the following dictionary:

 infix dictionary using OFNT9BE1.dat {
    str CMDORNUM  1-7
    str WHPREFIX  8-9
    str WHSENCMP  10-12
    str WHEXSTAT  13-42
    str WHSTACOD  43-72
    str DTOFUPDT  73-82
    str TMOFUPDT  83-90
}

which I saved into "OFNT9BE1.dct". You can then input the whole database using:

clear
infix using "OFNT9BE1.dct"

and here are the first 5 data observations

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 CMDORNUM str2 WHPREFIX str3 WHSENCMP str30(WHEXSTAT WHSTACOD) str10 DTOFUPDT str8 TMOFUPDT
"0000008" "05" "001" "NON EXTRADITABLE" "ARRESTED" "2000-03-23" "08:36:39"
"0000020" "01" "001" "NON EXTRADITABLE" "ARRESTED" "1995-12-04" "15:29:55"
"0000020" "BA" "001" "NON EXTRADITABLE" "ARRESTED" "1995-12-12" "15:13:04"
"0000028" "07" "001" "NON EXTRADITABLE" "ARRESTED" "2000-10-09" "08:03:41"
"0000028" "BA" "001" "EXTRADITE"        "ARRESTED" "1997-01-27" "16:54:27"
end

[Miles Like]

Thanks for responding Carole. I'm not sure I understand, is this using StatTransfer?

[Miles Like]

Thank you so much Robert. That was a tremendous help.