12

USA data on death can be obtained via Social Security as SSDI (death index) and SSDMF (death master file). http://en.wikipedia.org/wiki/Social_Security_Death_Index

One site http://ssdmf.info offers the data as text file.

1. Is there a public script that parses this data into a better format (e.g., CSV and explains what some other fields mean. (or can someone help to write it)

427528265JOHNSON                 JULIA          M              P0414200509201929                     
427528266MATTHEWS                JAMES          C               0318199408251930                     
427528270FOKAKIS                 ARTHUR                        V0726199607141919

2. The format seem to be SS, name, birth and death. Some records have V or P in front of birth. What does that mean?

4 Answers 4

7

As noted, there is no official public version of the file, because at this time the NTIS only provides it to subscribers.

As for parsing the file, I made a schema compatible with csvkit's in2csv utility. The schema can be downloaded from https://github.com/JoeGermuska/ffs/blob/master/us/ssa/death_master_file.csv

Once you've installed csvkit and downloaded a copy of the schema, the command would be

in2csv -s death_master_file.csv ssdm1 > ssdm1.csv

where ssdm1 might vary based on which of the ZIP files you retrieved from ssdmf.info

For more on the in2csv script, see readthedocs

5

I have a copy of an old version of the SSDI Master File, purchased (legally!) about a year ago through an online data broker for about $37. From what I can glean, one may either do a one-time purchase of the file OR may become a subscriber to the weekly or monthly updates. To quote from the PDF that was distributed with the file:

It is mandatory that all subscribers of the DMF intending to use its data on a continuing basis must, after receiving an updated complete DMF FULL FILE, keep that file updated by continually purchasing all MONTHLY OR WEEKLY UPDATES (NEW DEATHS/CHANGES/DELETIONS), beginning with the same month as the Full File. If you are not meeting SSA’s requirements because you are not receiving the MONTHLY OR WEEKLY UPDATES ON A CONTINUING BASIS immediately after receiving the FULL FILE, then you are NOT keeping your DMF up-to-date with SSA’s records. Thus, you are working with a DMF with an increased number of unnecessary inaccuracies and possibly adversely affecting an increased number of individuals. NO ONE IS TO SELL THE DMF WITHOUT REQUIRING CONTINUOUS SUBSCRIBERS TO ADHERE TO THIS MANDATORY REQUIREMENT FOR KEEPING THEIR DMF UP-TO-DATE.

Notwithstanding the Social Security Administration's apparent overuse of capital letters, it looks like subscribers are required to keep subscribing, so that their data is never stale, but one-time purchasers are not required to subscribe.

The version of the file I have is from mid-2010, but this is actually better for my (genealogical) purposes, as it contains a number of death records that were initially contributed by individual states, but then scrubbed out of subsequent releases of the database. The "V" or "P" is indeed indicative of "Verified" or "Proof", described in the file like this:

V = (VERIFIED) REPORT VERIFIED WITH A FAMILY MEMBER OR SOMEONE ACTING ON BEHALF 
OF THE FAMILY, OR ...
P = (PROOF) DEATH CERTIFICATE OBSERVED.
(NOTE: DMF SUBSCRIBERS MUST NOT TAKE ANY ADVERSE ACTION AGAINST ANY INDIVIDUAL 
WITHOUT FURTHER INVESTIGATION TO VERIFY ANY DEATH LISTED, EVEN IF V OR P CODES 
ARE PRESENT.

Here is the full data table provided in that PDF explaining the format of the records:

Death Master File Extract Output Record Specification 09/2001

Record Location   Field Description                                 Field Size
==============================================================================
01                Blank or A (add) C (change) or D (delete)         1
02-10             Social Security Number                            9
11-30             Last Name                                         20*
31-34             Name Suffix                                       4*
35-49             First Name                                        15*
50-64             Middle Name                                       15*
65                VorPCode (Verify or Proof code)                   1*
66-73             Date of Death (MM,DD,CC,YY)                       8*
74-81             Date of Birth (MM,DD,CC,YY)                       8*
82-83             State/Country Code of Residence /1                2*
84-88             Zip code Last Residence                           5*
89-93             Zip code Lump Sum Payment                         5*
94-100            Blanks                                            7

Online and CDROM specifications:
Record Length: 100 - WITH LINE FEED CHARACTER ADDED AT END
**Note: the record length will be 101 because there is a line feed character at the 
end of both the ASCII file and the EBCDIC online file, and the ASCII CDROM.
/1 = No new information after 2/88


DMF – STATE/COUNTRY CODE RESIDENCE (09/01)
====================================================================
CODE                                 CODE
    01    ALABAMA                        35    NORTH DAKOTA
    02    ALASKA                         36    OHIO
    03    ARIZONA                        37    OKLAHOMA
    04    ARKANSAS                       38    OREGON
    05    CALIFORNIA                     39    PENNSYLVANIA
    06    COLORADO                       40    PUERTO RICO
    07    CONNECTICUT                    41    RHODE ISLAND
    08    DELAWARE                       42    SOUTH CAROLINA
    09    DISTRICT OF COLUMBIA           43    SOUTH DAKOTA
    10    FLORIDA                        44    TENNESSEE
    11    GEORGIA                        45    TEXAS
    12    HAWAII                         46    UTAH
    13    IDAHO                          47    VERMONT
    14    ILLINOIS                       48    VIRGIN ISLANDS
    15    INDIANA                        49    VIRGINIA
    16    IOWA                           50    WASHINGTON
    17    KANSAS                         51    WEST VIRGINIA
    18    KENTUCKY                       52    WISCONSIN
    19    LOUISIANA                      53    WYOMING
    20    MAINE                          54    AFRICA
    21    MARYLAND                       55    ASIA
    22    MASSACHUSETTS                  56    CANADA
    23    MICHIGAN                       57    CENTRAL AMERICA & WEST INDIES
    24    MINNESOTA                      58    EUROPE
    25    MISSISSIPPI                    59    MEXICO
    26    MISSOURI                       60    OCEANIA (AUSTRALIA & ISLANDS IN THE PACIFIC
    27    MONTANA                        61    PHILIPPINE ISLANDS
    28    NEBRASKA                       62    SOUTH AMERICA
    29    NEVADA                         63    AREAS UNDER U.S. ADMINISTRATION: (CANAL ZONE, 
                                         CANTON ISLANDS, CAROLINE ISLANDS, MARIANA ISLANDS 
                                         (OTHER THAN GUAM), MARSHALL ISLANDS, MIDWAY ISLANDS, 
                                         WAKE ISLANDS)
    30    NEW HAMPSHIRE                  64    AMERICAN SAMOA
    31    NEW JERSEY                     65    GUAM
    32    NEW MEXICO                     33    NEW YORK
    34    NORTH CAROLINA

    (NOTE: IF ANY ALPHA’S/OR OTHER NUMERICS SHOW IN THESE TWO CODE POSITIONS, 
    YOU SHOULD IGNORE THEM. FOR PURPOSES, IT MEANS THAT SSA DOES NOT HAVE A
    STATE/COUNTRY CODE FOR YOU.)
2
  • Current pricing is $1825 for initial purchase and $2000+ for updates subscription. The purpose is to do research - e.g., life expectancy - how it changes in 1995 vs 2005
    – userJT
    May 22, 2013 at 2:45
  • 1
    It's public data, created for public use with public funds; it ought to be open data, freely available, and accessible through an API. Their current pricing and use constraints make me want to go all HULK SMASH.
    – Asparagirl
    May 22, 2013 at 4:05
3

Interesting. From the comments on http://ssdmf.info, it does seem the file is only available for purchase from the Social Security Administration.

ssdmf.info includes a PDF that the SSA requires to be distributed with the data, and it contains some information about the schema.

http://ssdmf.info/blah-blah-blah.pdf

The file contains some limited information about the schema. V/P seems to stand for VERIFIED or PROOF CODE.

As for how to parse the file, I would suggest using OpenRefine. It deals quite well with fixed-width text files. You can find the field widths in the aforementioned PDF, as well.

0

I created a python 3 script to convert the data to CSV:

f = open("ssdm1")
for line_number, line in enumerate(f):
    ssn = line[1:10]
    lname = line[10:20].strip()
    suffix= line[30:33].strip()
    fname = line[34:48].strip()
    mname = line[49:63].strip()
    vpcode= line[64:64]     # (V)erified or (P)roof code

    dod = line[65:73]
    dob = line[73:81]
    print(ssn[0:3] + '-' + ssn[3:5] + '-' + ssn[5:9] + ',', 
        lname + ',' + fname + ',' + mname + ',', 
        dob[0:2] + '/' + dob[2:4] + '/' + dob[4:8] + ',',
        dod[0:2] + '/' + dod[2:4] + '/' + dod[4:8] )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.