## How to fill in gaps in between?

Solved
Frequent Contributor
Posts: 122

# How to fill in gaps in between?

Hi, I have a dataset.

permno               month                    return

10                         1                              0.2

10                         2                              0.3

10                         5                              0.1

11                         2                              0.8

11                         4                              0.1

I want to fill in gaps between month ids and set returns to zero for filled-in observations. And I only want to fill in gaps within permno. That is, for permno 10, I need observations for month 3 and 4. For permno 11, only month 3 is needed, NOT month 1 or 5.

permno               month                    return

10                         1                              0.2

10                         2                              0.3

10                         3                              0

10                         4                              0

10                         5                              0.1

11                         2                              0.8

11                         3                              0

11                         4                              0.1

I checked proc expand but didn't find a proper way to do it.

Accepted Solutions
Solution
‎09-12-2014 08:36 AM
Super User
Posts: 10,850

## Re: How to fill in gaps in between?

```data have;
input permno               month                    return ;
cards;
10                         1                              0.2
10                         2                              0.3
10                         5                              0.1
11                         2                              0.8
11                         4                              0.1
;
run;

data want(drop=_: i);
merge have have(firstobs=2 keep=permno month rename=(permno=_permno month=_month));
output;
if permno=_permno then do;
do i=month+1 to _month-1;
month=i;return=0;output;
end;
end;
run;

```

Xia Keshan

All Replies
Super User
Posts: 8,218

## Re: How to fill in gaps in between?

You could do that with a datastep.  e.g.,

data want (drop=hold: last;

set have;

by permno;

last_month=lag(month);

if month-last_month ne 1 and not first.permno then do;

hold_month=month;

hold_return=return;

do month=last_month+1 to hold_month-1;

return=0;

output;

end;

month=hold_month;

return=hold_return;

output;

end;

else output;

run;

Solution
‎09-12-2014 08:36 AM
Super User
Posts: 10,850

## Re: How to fill in gaps in between?

```data have;
input permno               month                    return ;
cards;
10                         1                              0.2
10                         2                              0.3
10                         5                              0.1
11                         2                              0.8
11                         4                              0.1
;
run;

data want(drop=_: i);
merge have have(firstobs=2 keep=permno month rename=(permno=_permno month=_month));
output;
if permno=_permno then do;
do i=month+1 to _month-1;
month=i;return=0;output;
end;
end;
run;

```

Xia Keshan

🔒 This topic is solved and locked.