topic Re: how to add data automatically to the missing days in historical prices according to the prev. pr in SAS Programming

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

chinaski — Tue, 11 Jan 2022 09:11:36 GMT

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.

Re: how to add data automatically to the missing days in historical prices according to the prev. pr

Kurt_Bremser — Tue, 11 Jan 2022 09:28:03 GMT

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;

Re: how to add data automatically to the missing days in historical prices according to the prev. pr

chinaski — Tue, 11 Jan 2022 13:40:10 GMT

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;