how to add data automatically to the missing days in historical prices according to the prev. price

[chinaski]

I wonder how to add data automatically to the missing days in historical prices according to previous available days price.

My sample data is attached, as you see i have data for the first three days of december and my next available data is on the 10th of december.

What i want is to expand my table from 4th of december to the 9th of december with the previous price which is 6900 and so on.

I also attached a second file which is the expanded table i need as a result.

Any help would be so much appreciated.Thanks.

[Kurt_Bremser]

Please provide data in usable form. Excel files are not usable as SAS datasets, but data steps with datalines are.

See an example for a "look-ahead":

data have;
input day :yymmdd10. price;
format day yymmdd10.;
datalines;
2021-12-01 3
2021-12-02 3
2021-12-03 4
2021-12-10 5
;

data want;
merge
  have
  have (
    firstobs=2
    keep=day
    rename=(day=_day)
  )
  end=done
;
output;
if not done then
do day = day + 1 to _day - 1;
  output;
end;
drop _day;
run;

[chinaski · Accepted solution]

I managed to figure out the solution, for those who would need the code i put here:

If the previous price exists and if there is no price in at least one of the following days, we take it from the previous one.

proc timeseries data=work.sample_data out=sample_serie;
by WEB_SITE PRODUCT_NAME;
id DATE interval=day
accumulate=mean
setmiss=previous
start='01dec2021'd
end ='31dec2021'd;
var product_price;
run;

If there is no price on the first day of the month, we get it from the next available data.

proc timeseries data=work.sample_seri out=sample_serie2;
by WEB_SITE PRODUCT_NAME;
id DATE interval=day
accumulate=mean
setmiss=next
start='01dec2021'd
end ='31dec2021'd;
var product_price;
run;