Quartz | Level 8

## How to fill in some missing years in my data set

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How to fill in some missing years in my data set

``````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;``````
3 REPLIES 3
Barite | Level 11

## Re: How to fill in some missing years in my data set

``````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;``````
Super User

## Re: How to fill in some missing years in my data set

``````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;``````
Quartz | Level 8

## Re: How to fill in some missing years in my data set

Wow! thanks a lot, Xia. That was so smart and neat.
Discussion stats
• 3 replies
• 1892 views
• 3 likes
• 3 in conversation