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,
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;
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;
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;
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.