LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (January 2010, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 5 Jan 2010 12:42:55 -0500
Reply-To:     msz03@albany.edu
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Zdeb <msz03@ALBANY.EDU>
Subject:      Re: Replace missing address with most recent address? Thank you!
Content-Type: text/plain;charset=iso-8859-1

hi ... hmmm, I see you only got one reply (and I don't think that works) maybe this is easier than I think, but ...

if I interpret your question correctly, you first have to find the observation within an ID that has the maximum value of DATE and a non-missing ADDRESS that non-missing ADDRESS gets substituted for all blank addresses within an ID

if that's the problem, then I think you need two passes through the data, first to find the latest non-missing ADDRESS, second to replace blank addresses

if the above is correct, here are some ideas ... (I guess here are some ideas even if the above is not correct !!!)

data one; input id address : $4. date : mmddyy.; format date mmddyy10.; datalines; 1 . 01/01/2009 1 abcd 01/05/2009 1 cdef 01/06/2009 2 defg 01/02/2009 2 adbc 01/05/2009 2 . 01/03/2009 ; run;

* just in case not sorted by ID; proc sort data=one; by id; run;

* find latest real address within an ID; proc sql; create table fix as select address as addr from one where address is not missing group by id having date eq max(date) ; quit;

* replace blank address within an ID with latest real address; data two; do until(last.id); set one; by id; if first.id then set fix; if address eq ' ' then address = addr; output; end; drop addr; run;

title 'fixed with sql+data step'; proc print data=two; run;

* another approach if you don't mind having the original data set changed to date-order within an ID;

data one; input id address : $4. date : mmddyy.; format date mmddyy10.; datalines; 1 . 01/01/2009 1 abcd 01/05/2009 1 cdef 01/06/2009 2 defg 01/02/2009 2 adbc 01/05/2009 2 . 01/03/2009 ; run;

proc sort data=one; by id date; run;

data two; do until (last.id); set one (where=(address is not missing) in=pass1) one (in=pass2); by id; * first pass within an ID ... latest non-missing address will be placed in FIX; if pass1 then fix = address; * second pass within an ID ... replace missing addresses; if pass2 then do; if address eq '' then address = fix; output; end; end; drop fix; run;

title 'fixed with Howard Schreier self-interleave'; proc print data=two; run;

-- Mike Zdeb U@Albany School of Public Health One University Place Rensselaer, New York 12144-3456 P/518-402-6479 F/630-604-1475

> Hello All, > > I have a dats set as: > > ID address date > 1 01/01/2009 > 1 abcd 01/05/2009 > 1 cdef 01/06/2009 > 2 defg 01/02/2009 > 2 adbc 01/05/2009 > 2 01/03/2009 > > How I can replace the missing address for seperated ID with the filled late recent address? > > Thank you so much! > > Nancy >


Back to: Top of message | Previous page | Main SAS-L page