Repeat Index Date

[ifti_ch2002]

Hi,

I have following data

data have;
infile datalines missover; 
   input IDnumber @3 date  mmddyy10. index_date  mmddyy10.;
   datalines;  
1 4-7-2003 4-7-2003
1 4-7-2009 4-7-2009
1 4-7-2010 
1 4-7-2011 
1 4-7-2012 
1 4-7-2013 
1 4-7-2014 4-7-2014
1 4-7-2015 
1 4-7-2016 
1 4-7-2018 
2 4-7-2005 4-7-2005
2 4-7-2011 4-7-2011 
2 4-7-2012 
2 4-7-2013 
2 4-7-2014 
2 4-7-2015 
2 4-7-2016 4-7-2016
2 4-7-2017 
2 4-7-2018 
2 4-7-2019 
2 4-7-2020 
;       
run; 

What i need is to repeat the index date until the new index date. As following

data want;
infile datalines missover; 
   input IDnumber @3 date  mmddyy10. @12 index_date  mmddyy10.;
   datalines;  
1 4-7-2003  4-7-2003
1 4-7-2009  4-7-2009
1 4-7-2010  4-7-2009
1 4-7-2011  4-7-2009
1 4-7-2012 4-7-2009
1 4-7-2013 4-7-2009
1 4-7-2014 4-7-2014
1 4-7-2015 4-7-2014
1 4-7-2016 4-7-2014
1 4-7-2018 4-7-2014
2 4-7-2005 4-7-2005
2 4-7-2011 4-7-2011
2 4-7-2012 4-7-2011
2 4-7-2013 4-7-2011
2 4-7-2014 4-7-2011
2 4-7-2015 4-7-2011
2 4-7-2016 4-7-2016
2 4-7-2017  4-7-2016
2 4-7-2018  4-7-2016
2 4-7-2019  4-7-2016
2 4-7-2020  4-7-2016
;       
run; 

[novinosrin · Accepted solution]

data have;
infile datalines missover; 
   input IDnumber date  :mmddyy10. index_date :mmddyy10.;
   format date index_date mmddyy10.;
   datalines;  
1 4-7-2003 4-7-2003
1 4-7-2009 4-7-2009
1 4-7-2010 
1 4-7-2011 
1 4-7-2012 
1 4-7-2013 
1 4-7-2014 4-7-2014
1 4-7-2015 
1 4-7-2016 
1 4-7-2018 
2 4-7-2005 4-7-2005
2 4-7-2011 4-7-2011 
2 4-7-2012 
2 4-7-2013 
2 4-7-2014 
2 4-7-2015 
2 4-7-2016 4-7-2016
2 4-7-2017 
2 4-7-2018 
2 4-7-2019 
2 4-7-2020 
;       
run; 

data want;
 update have(obs=0) have;
 by IDnumber;
 output;
run;

proc print noobs;run;

[ifti_ch2002]

Thanks, we can even do with retain 🙂

[Jagadishkatam]

please try the below code and check index_date2

data have;
infile datalines missover; 
   input IDnumber date :mmddyy10. index_date : mmddyy10.;
   datalines;  
1 4-7-2003 4-7-2003
1 4-7-2009 4-7-2009
1 4-7-2010 .
1 4-7-2011 .
1 4-7-2012 .
1 4-7-2013 .
1 4-7-2014 4-7-2014
1 4-7-2015 .
1 4-7-2016 .
1 4-7-2018 .
2 4-7-2005 4-7-2005
2 4-7-2011 4-7-2011 
2 4-7-2012 .
2 4-7-2013 .
2 4-7-2014 .
2 4-7-2015 .
2 4-7-2016 4-7-2016
2 4-7-2017 .
2 4-7-2018 .
2 4-7-2019 .
2 4-7-2020 .
;       
run; 

data want;
set have;
by IDnumber notsorted;
retain index_date2;
if first.IDnumber then index_date2=.;
if index_date ne . then index_date2=index_date;
format index_date index_date2 date date9.;
run;

[PaigeMiller]

data have;
infile datalines missover; 
   input IDnumber @3 date  mmddyy. @12 index_date  mmddyy10.;
   retain index_date1;
   if not missing(index_date) then index_date1=index_date;
   format date index_date1 mmddyy10.;
   datalines;  
1 4-7-2003 4-7-2003
1 4-7-2009 4-7-2009
1 4-7-2010 
1 4-7-2011 
1 4-7-2012 
1 4-7-2013 
1 4-7-2014 4-7-2014
1 4-7-2015 
1 4-7-2016 
1 4-7-2018 
2 4-7-2005 4-7-2005
2 4-7-2011 4-7-2011 
2 4-7-2012 
2 4-7-2013 
2 4-7-2014 
2 4-7-2015 
2 4-7-2016 4-7-2016
2 4-7-2017 
2 4-7-2018 
2 4-7-2019 
2 4-7-2020 
;       
run; 

[novinosrin]

Boring afternoon and so just for some fun

data have;
infile datalines missover; 
   input IDnumber date  :mmddyy10. index_date :mmddyy10.;
   format date index_date mmddyy10.;
   datalines;  
1 4-7-2003 4-7-2003
1 4-7-2009 4-7-2009
1 4-7-2010 
1 4-7-2011 
1 4-7-2012 
1 4-7-2013 
1 4-7-2014 4-7-2014
1 4-7-2015 
1 4-7-2016 
1 4-7-2018 
2 4-7-2005 4-7-2005
2 4-7-2011 4-7-2011 
2 4-7-2012 
2 4-7-2013 
2 4-7-2014 
2 4-7-2015 
2 4-7-2016 4-7-2016
2 4-7-2017 
2 4-7-2018 
2 4-7-2019 
2 4-7-2020 
;       
run; 

data want;
 if _n_=1 then do;
  dcl hash H () ;
  h.definekey  ("IDnumber") ;
  h.definedata ("index_date") ;
  h.definedone () ;
 end;
 do _n_=h.clear() by 0 until(last.IDnumber);
  set have;
  by IDnumber;
  if nmiss(index_date) then h.find();
  else h.replace();
  output;
 end;
run;
proc print noobs;run;