How to fill in gaps in between?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

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
Grand Advisor
Posts: 9,593

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

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,297

Re: How to fill in gaps in between?

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

data want (drop=hold: lastSmiley Happy;

  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
Grand Advisor
Posts: 9,593

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.

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

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