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.
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;
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.
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;
Something the Lord made...
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.