How to fill in some missing years in my data set

[Shayan2012]

Hi All,

So, I have this dataset:

data have;
     input year $ ID $ number;
     cards;
     1977  A  23
     1978 A  27
     1984 B  99
     1987 B  101
     1991 B   107
     1953 C  11
     1957 C   17
;

What I want to do is to fill in the missing in between years for each ID. For example, A has no missing becasue there is no missing year between the beginning and end of its years. However, I want to fill in for B with years 1985, 1986, and also 1988, 89, and 90. [ The other columns will be the same as the previous year]

So this is the WANT:

1977 A 23

1978 A 27

1984 B 99

1985 B 99

1986 B 99

1987 B 101

1988 B 101

1989 B 101

1990 B 101

1991 B 107

1953 C 11

1954 C 11

1955 C 11

1956 C 11 

1957 C 11

I am aware of Proc Expand but did not find a way of working through different IDs. What is the best way to perform this exercise? Thanks a lot,

[mohamed_zaki]

data want;
set have;
retain pyear pnumber;
tempy=year; tempn=number;
by id;
if first.id then do; pyear=year; pnumber=number;end;
else if year - pyear > 1 then do i=1 to (year-pyear-1);
	year=pyear+i;
	number=pnumber;
	output;
end;
year=tempy; number=tempn;
pyear=year; pnumber=number;
output;
drop pyear pnumber tempy tempn i;
run;

[Ksharp · Accepted solution]

data have;
     input year  ID $ number;
     cards;
     1977  A  23
     1978 A  27
     1984 B  99
     1987 B  101
     1991 B   107
     1953 C  11
     1957 C   17
;
run;

data want;
 merge have have( firstobs=2 keep=id year rename=(id=_id year=_year));
 output;
 if id=_id then do;
  do i=year+1 to _year-1;
   year=i;output;
  end;
 end;
 drop _: i;
run;

[Shayan2012]

Wow! thanks a lot, Xia. That was so smart and neat.