BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shayan2012
Quartz | Level 8

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
Ksharp
Super User
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

3 REPLIES 3
mohamed_zaki
Barite | Level 11
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
Super User
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
Quartz | Level 8
Wow! thanks a lot, Xia. That was so smart and neat.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1891 views
  • 3 likes
  • 3 in conversation