DATA Step, Macro, Functions and more

How to fill in some missing years in my data set

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

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,


Accepted Solutions
Solution
‎02-22-2016 11:11 AM
Super User
Posts: 10,020

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

Posted in reply to Shayan2012
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;

View solution in original post


All Replies
Super Contributor
Posts: 490

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

Posted in reply to Shayan2012
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;
Solution
‎02-22-2016 11:11 AM
Super User
Posts: 10,020

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

Posted in reply to Shayan2012
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;
Frequent Contributor
Posts: 75

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

Wow! thanks a lot, Xia. That was so smart and neat.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 286 views
  • 2 likes
  • 3 in conversation