Filling missing dates from last and first dates

[Zakir]

Can anyone please help me to figure out how to fill the missing dates from the following data set. I appreciate your kind help. Thanks in advance.

Data set:

ISIN                     FY

AAA                    31/12/2008

AAA                    31/12/2009

AAA                    .

BBB                    .

BBB                    30/06/2008

BBB                    .

CCC                   .

CCC                   .

CCC                   31/12/2010

Kind regards,

Zakir

[Kurt_Bremser]

If you have more than one non-missing date for a ISIN, which one should take precedence?

[PeterClemmensen]

data have;
input ISIN $ FY :ddmmyy10.;
format FY ddmmyy10.;
datalines;
AAA 31/12/2008
AAA 31/12/2009
AAA .
BBB .
BBB 30/06/2008
BBB .
CCC .
CCC .
CCC 31/12/2010
;

data want(drop=_:);
   do until (last.ISIN);
      set have;
      by ISIN;
      if FY ge _max then _max=FY;     
   end;
   do until (last.ISIN);
      set have;
      by ISIN;
      if FY=. then FY=_max; 
      output; 
   end;
run;

[Zakir]

Hi,

First let me thank you for your effort. The code is working but it did not yield what I wanted. May be I could not properly make the point clear. Let me restate the problem. 

Data I have:

ISIN             FY

AAA           31/12/2008

AAA           31/12/2009

AAA           .

BBB          .

BBB          30/06/2008

BBB          .

CCC         .

CCC         .

CCC        31/12/2010

Data I want:

ISIN             FY

AAA           31/12/2008

AAA           31/12/2009

AAA           31/12/2010

BBB           30/06/2007

BBB           30/06/2008

BBB           30/06/2009

CCC          31/12/2008

CCC          31/12/2009

CCC          31/12/2010

Can you please help me!! Sorry for any inconveniences.

Kind regards,

Zakir

[Kurt_Bremser]

Straight forward:

data want1;
set have;
by isin;
retain _fy;
number = _n_; /* needed for later re-sorting */
if first.isin then _fy = .;
if fy ne .
then _fy = fy;
else fy = _fy;
drop _fy;
run;

proc sort data=want1;
by descending number;
run;

data want2;
set want1;
by descending isin;
retain _fy;
if first.isin then _fy = .;
if fy ne .
then _fy = fy;
else fy = _fy;
drop _fy;
run;

proc sort
  data=want2
  out=want (drop=number)
;
by number;
run;

Assuming that have is sorted by isin.

[Ksharp]

You didn't post the ouput you need yet .

data have;
input ISIN $ FY :ddmmyy10.;
format FY ddmmyy10.;
datalines;
AAA 31/12/2008
AAA 31/12/2009
AAA .
BBB .
BBB 30/06/2008
BBB .
CCC .
CCC .
CCC 31/12/2010
;

data temp;
 set have;
 by isin;
 if first.isin then n=0;
 n+1;
run;
data key;
 set temp(where=(fy is not missing));
 by isin;
 if first.isin;
run;
data want;
 merge temp key(keep=fy n isin rename=(fy=_fy n=_n));
 by isin;
 if missing(fy) then fy=intnx('month',_fy,n-_n,'s');
 drop _:;
run;

[Zakir]

Hi,

First let me thank you for your effort. The code is working but It did not yield what I wanted. May be I could not properly make the point clear. Let me restate the problem. 

Data I have:

ISIN             FY

AAA           31/12/2008

AAA           31/12/2009

AAA           .

BBB          .

BBB          30/06/2008

BBB          .

CCC         .

CCC         .

CCC        31/12/2010

Data I want:

ISIN             FY

AAA           31/12/2008

AAA           31/12/2009

AAA           31/12/2010

BBB           30/06/2007

BBB           30/06/2008

BBB           30/06/2009

CCC          31/12/2008

CCC          31/12/2009

CCC          31/12/2010

Can you please help me!! Sorry for any inconveniences.

Kind regards,

Zakir

[Ksharp · Accepted solution]

OK. There is some typo in my code. Change 'month' into 'year' :

data have;
input ISIN $ FY :ddmmyy10.;
format FY ddmmyy10.;
datalines;
AAA 31/12/2008
AAA 31/12/2009
AAA .
BBB .
BBB 30/06/2008
BBB .
CCC .
CCC .
CCC 31/12/2010
;

data temp;
 set have;
 by isin;
 if first.isin then n=0;
 n+1;
run;
data key;
 set temp(where=(fy is not missing));
 by isin;
 if first.isin;
run;
data want;
 merge temp key(keep=fy n isin rename=(fy=_fy n=_n));
 by isin;
 if missing(fy) then fy=intnx('year',_fy,n-_n,'s');
 drop _:;
run;