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

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

lior — Wed, 26 Nov 2014 23:07:53 GMT

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

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

stat_sas — Thu, 27 Nov 2014 00:04:55 GMT

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;

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

art297 — Thu, 27 Nov 2014 00:42:02 GMT

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;

run;

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

Haikuo — Thu, 27 Nov 2014 01:18:50 GMT

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;

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

lior — Thu, 27 Nov 2014 06:39:22 GMT

thank you.