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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.