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
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;
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;
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;
thank you.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.