read a text file from the census!

[vxhong17]

Hi everyone,

Could you  let me know how to input the text file in the following link?

http://www.census.gov/population/metro/files/lists/historical/90mfips.txt

Thank you so much for your help,

[RichardinOz]

I think the easiest way is to copy the file to a temporary location, say C:\temp\90mfips.txt.   To preserve the metadata in the indentation of the name or title I suggest creating 'Level' flag, and an accompanying format that specifies the type of name:

Proc Format ;

  Value $Name_Title

  '1' = 'MSA/CMSA title'

  '2' = 'PMSA title'

  '3' = 'Component county name'

  '4' = 'Component city or town name (in New England only)'

  ;

  Value $Centrl

  '1' = 'Central or city'

  '2' = 'Outlying or town'

  ;

You can then strip out all the extra (non data) rows, including the column headers and the layout information at the end of the temporary copy of the file after using the metadata (AKA File Layout) to create column definitions and use column input.  Add 4 extra temporary flags (P1 - P4) to detect the name indentation.

The following code is untested so might require some tweaking - Labels might be truncated.

Data    Met_Areas ;

    Infile    'C:\temp\90mfips.txt' ;

    Length    MSA_CMSA    $4

        PMSA        $4

        Alt_CMSA    $2

        State        $2

        County        $3

        Central_Outlying    $1

        Entity        $5

        Name_Title    $51

        Level        $1

        P1 - P4        $1           

        ;

    Format    Level    $Name_Title.

       Central_Outlying  $Centrl.

       ;   

    Label    MSA_CMSA    = 'Four-digit FIPS MSA/CMSA code (6/30/90 definition)'

        PMSA        = 'Four-digit FIPS PMSA code (6/30/90 definition)'

        Alt_CMSA    = 'Alternative two-digit FIPS CMSA code (6/30/90 definition)'

        State        = 'Two-digit FIPS state code (blank at MA level)'

        County        = 'Three-digit FIPS county code (blank at MA level)'

        Central_Outlying

                = 'Central/Outlying county or city/town flag (1 = central, 2 = Outlying)'

        Entity        = 'Five-digit FIPS entity code (blank at MA and county levels)'

        Name_Title    = 'Name or Title'

        Level        = 'Name or Title Level'

        ;   

    Input    MSA_CMSA    $    1-4

        PMSA        $    9-12

        Alt_CMSA    $    17-18

        State        $    25-26

        County        $    27-29

        Central_Outlying    $    33

        Entity        $    41-45

        Name_Title    $    49-99

        P1        $    49

        P2        $    52

        P3        $    57

        P4        $    65

        ;       

    Select ;

        When (P1 > ' ')    Level = '1' ;

        When (P2 > ' ')    Level = '2' ;

        When (P3 > ' ')    Level = '3' ;

        When (P4 > ' ')    Level = '4' ;

        Otherwise ;

    End ;

    If    MSA_CMSA = ' '    then Delete ;

    Name_Title    =    Left (Name_Title) ;

    Drop    P1 - P4 ;

Run ;

Richard

[vxhong17]

Hi Richard,

Thank you so much for your help,