Reading a "complex" data file

[tesu]

Thank you guys. I thought I was dumb enough to fail to read this data. But it turns out that in fact this task is not an easy one. I will try your suggestions and get back to you.

[Tom · Accepted solution]

Does not look that hard. Just retain the MSA/CMSA level variables onto the records for the CITY/ENTITY level variables.

data msa_cty ;

  infile 'c:\downloads\cencty.txt' expandtabs  truncover;

  input @ ;

  if _infile_ ^=: ' ' then input MSANAME $1-48 MSA $49-52 CMSA $65-66 ;

  else input CCNAME $1-48 PMSA $57-60 state $73-74 entity $81-85 ;

  retain msaname msa cmsa ;

run;

[tesu]

Thank you very much, Tom. I learned a lot from your code! Actually, your code did not print MSA code for some large areas. But it is just because of a data problem. For example,

Norfolk-Virginia Beach-Newport News, VA-NC MSA. --> Here, the whole name is in one line. But,

New York-Northern New Jersey-Long Island,

NY-NJ-CT-PA CMSA

Philadelphia-Wilmington-Atlantic City,

PA-NJ-DE-MD CMSA 

NY and Philadelphia each takes two lines. I erased the second lines and got it right in the end.

[Tom]

Here is a way to handle that without modifying the input file.

data msa_cty ;

  length MSANAME $80 ;

  infile 'c:\downloads\cencty.txt' expandtabs  truncover;

  input @ ;

  if _infile_ ^=: ' ' then do;

     input MSANAME $1-48 MSA $49-52 CMSA $65-66 ;

     if msa||cmsa=' ' then do;

       input CCNAME $1-48 MSA $49-52 CMSA $65-66 ;

       MSANAME = catx(' ',msaname,ccname);

       CCNAME=' ';

     end;

  end;

  else input CCNAME $1-48 PMSA $57-60 state $73-74 entity $81-85 ;

  retain msaname msa cmsa ;

run;

[tesu]

Something the Lord made...