Entering rows of missing value and fill them with data from the previous row

[lior]

Hello,

I received from some database data of daily stock price that look like this:

DATADATE	Stock_Price
03/01/1984	14
04/01/1984	17
05/01/1984	15
06/01/1984	16
09/01/1984	17
10/01/1984	20
11/01/1984	18
12/01/1984	19
13/01/1984	21
16/01/1984	18
17/01/1984	20
18/01/1984	22
19/01/1984	23
20/01/1984	21
23/01/1984	17
24/01/1984	15

As you can see between 06/01/1984 and 09/01/1984 there are missing dates, also between 13/01/1984 and 16/01/1984 Etc.

Is there a way to run a statement that enter the missing dates and assign to them the Stock_price of the previous date?

So the report will look like that:

DATADATE	Stock_Price
03/01/1984	14
04/01/1984	17
05/01/1984	15
06/01/1984	16
07/01/1984	16
08/01/1984	16
09/01/1984	17
10/01/1984	20
11/01/1984	18
12/01/1984	19
13/01/1984	21
14/01/1984	21
15/01/1984	21
16/01/1984	18
17/01/1984	20
18/01/1984	22
19/01/1984	23
20/01/1984	21
21/01/1984	21
22/01/1984	21
23/01/1984	17
24/01/1984	15

thanks, 

Lior

[stat_sas]

data have;

input DATADATE :ddmmyy10. Stock_Price;

format DATADATE ddmmyy10.;

datalines;

03/01/1984 14

04/01/1984 17

05/01/1984 15

06/01/1984 16

09/01/1984 17

10/01/1984 20

11/01/1984 18

12/01/1984 19

13/01/1984 21

16/01/1984 18

17/01/1984 20

18/01/1984 22

19/01/1984 23

20/01/1984 21

23/01/1984 17

24/01/1984 15

;

data base;

do DATADATE="03jan1984"d to "24jan1984"d by 1;

output;

end;

format DATADATE ddmmyy10.;

run;

data want(drop=Stock_Price rename=(Stock_Price1=Stock_Price));

merge base(in=a) have(in=b);

retain Stock_Price1;

if Stock_Price ne . then Stock_Price1=Stock_Price;

by DATADATE;

if a;

run;

[art297 · Accepted solution]

Here is another way that doesn't require hardcoding the date range:

data want (keep=datadate Stock_Price);

  set have nobs=numobs;

  pointer=_n_+1;

  output;

  if _n_ lt numobs then do;

    set have (keep=datadate rename=(datadate=nextdate)) point=pointer;

    if datadate+1 lt nextdate then do;

      nextd=datadate+1;

      do datadate=nextd to nextdate-1;

        output;

      end;

    end;

  end;

run;

[lior]

thank you.

[Haikuo]

Aside of 'look ahead' technique offered by   ,  another approach using 'look back' , takes one pass to the data:

data have;

     input DATADATE :ddmmyy10. Stock_Price;

     format DATADATE ddmmyy10.;

     datalines;

03/01/1984 14

04/01/1984 17

05/01/1984 15

06/01/1984 16

09/01/1984 17

10/01/1984 20

11/01/1984 18

12/01/1984 19

13/01/1984 21

16/01/1984 18

17/01/1984 20

18/01/1984 22

19/01/1984 23

20/01/1984 21

23/01/1984 17

24/01/1984 15

;

data want;

     set have;

     _lagsp=lag(stock_price);

     _ldt=lag(datadate);

     if dif(datadate)>1 then

           do;

                _sp=Stock_Price;

                stock_price=_lagsp;

                _dt=datadate;

                do datadate=_ldt+1 to _dt-1;

                     output;

                end;

                stock_price=_sp;

           end;

     output;

     drop _:;

run;