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.
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;
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;
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;
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.