topic Re: Filling missing dates from last and first dates in SAS Programming

Filling missing dates from last and first dates

Zakir — Sat, 18 May 2019 04:47:29 GMT

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 31/12/2010

Kind regards,

Zakir

Re: Filling missing dates from last and first dates

Kurt_Bremser — Sat, 18 May 2019 06:12:31 GMT

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

Re: Filling missing dates from last and first dates

PeterClemmensen — Sat, 18 May 2019 09:05:35 GMT

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;

Re: Filling missing dates from last and first dates

Ksharp — Sat, 18 May 2019 11:34:57 GMT

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;

Re: Filling missing dates from last and first dates

Zakir — Mon, 20 May 2019 05:28:17 GMT

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 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

Re: Filling missing dates from last and first dates

Zakir — Mon, 20 May 2019 05:29:48 GMT

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 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

Re: Filling missing dates from last and first dates

Kurt_Bremser — Mon, 20 May 2019 06:51:30 GMT

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.

Re: Filling missing dates from last and first dates

Ksharp — Tue, 21 May 2019 12:00:52 GMT

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;