DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

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

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


Accepted Solutions
Solution
‎11-26-2014 07:42 PM
PROC Star
Posts: 7,467

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

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

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

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;

Solution
‎11-26-2014 07:42 PM
PROC Star
Posts: 7,467

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

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;

Contributor
Posts: 38

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

thank you.

Respected Advisor
Posts: 3,156

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

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;

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 256 views
  • 6 likes
  • 4 in conversation