topic Re: How to fill in some missing years in my data set in SAS Programming

How to fill in some missing years in my data set

Shayan2012 — Sun, 21 Feb 2016 15:56:51 GMT

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,

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

mohamed_zaki — Sun, 21 Feb 2016 21:55:10 GMT

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;

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

Ksharp — Mon, 22 Feb 2016 02:08:57 GMT

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;

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

Shayan2012 — Mon, 22 Feb 2016 16:12:20 GMT

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