Fill in missing data by id

Reply
Occasional Contributor
Posts: 19

Fill in missing data by id


Hello,

I am trying to fill in missing data in a stacked dataset and I can't quite get the result I'm looking for.

My data looks like this

scrssnyearFIPS
111-22-3333200809990
111-22-33332009
111-22-33332010

222-33-4444

200934567
222-33-4444201034567
333-44-5555201067897
333-44-55552011
333-44-5555201256789
333-44-55552013
333-44-5555201467899

I would like to fill in the blank for missing FIPS using the adjacent FIPS code, if not missing.  What's tricky (for me) is I need to use the next higher year's value (moving up the years) and if not available (or missing) then use the next adjacent moving down the years.  So for example, 333-44-5555 should have a FIPS code for 2013 = 67899; 2011 = 56789. 111-22-3333 should have 09990 for 2009 and 2010.

I found a few similar questions/answers on this site but not exactly what I'm looking for.  I think I need to sort by scrssn and descending year, then use the retain statement with the not missing(FIPS) and then repeat, sorting without descending year.  But, I can't quite get my code to work. 

Any ideas would be so helpful--thank you!

Kelly

Frequent Contributor
Posts: 85

Re: Fill in missing data by id

This does the trick - using your approach.

data have;
infile cards missover;
input scrssn :$11. year FIPS :$5.;
cards;
111-22-3333 2008 09990
111-22-3333 2009 
111-22-3333 2010 
222-33-4444 2009 34567
222-33-4444 2010 34567
333-44-5555 2010 67897
333-44-5555 2011 
333-44-5555 2012 56789
333-44-5555 2013 
333-44-5555 2014 67899
;
run;

proc sort data=have ;
  by scrssn  descending year;
run;

data ;
  set ;
  by scrssn;
  length prev_fips $5;
  retain prev_fips '';
  if first.scrssn then
  prev_fips = ''; 
  if FIPS = '' then
     fips = prev_fips;
  else
  prev_fips = fips;
  drop prev_fips;
run;

proc sort ;
  by scrssn  year;
run;

data  want ;
  set ;
  by scrssn;
  length prev_fips $5;
  retain prev_fips '';
  if first.scrssn then
  prev_fips = ''; 
  if FIPS = '' then
     fips = prev_fips;
  else
  prev_fips = fips;
  drop prev_fips;
run;

(I've let SAS name the temporary data sets, and use the previously created one for the next step.) 

There may be cleverer solutions.

Occasional Contributor
Posts: 19

Re: Fill in missing data by id

Thank you!!

Ask a Question
Discussion stats
  • 2 replies
  • 200 views
  • 3 likes
  • 2 in conversation