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
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;
If you have more than one non-missing date for a ISIN, which one should take precedence?
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;
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
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.
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.