My dataset contains a list of about 100 products with total sales by day.
Say for example the date range starts July 1st and ends July 18th.
Some products have dates & totals missing for July 1st, July 10th and July 18th.
How can I populate a new dataset that contains everything from the original dataset but with any missing dates for each product with total being 0?
Thanks
Fred
Create a data set that has the list of dates you need and then left join on the data sets.
ie
data all_dates;
start_date='01Jul2011'd;
end_date = '18Jul2011'd;
num_days=end_date-start_date+1;
do i=1 to num_days;
date=start_date+1;
total=0;
end;
run;
Then join the dataset to yours and verify the values are set to 0.
proc sql;
create table test1 as
select a.*, b.*
from all_dates a
left join have b
on a.date=b.date;
quit;
Another option is the lag function...check if the previous date-current-date is 1 but then you could have consecutive dates missing and things can get complex. You'll probably have to tweak the above code to get the date ranges correctly set and the joins to get the data you need.
I find this a simple and straightforward solution.
Fred,
Here is a rather brute force solution:
data have;
informat date date9.;
input product date total;
cards;
1 01jul2011 1
1 03jul2011 5
1 17jul2011 2
2 02jul2011 3
2 18jul2011 4
3 02jul2011 3
3 17jul2011 4
;
proc sql noprint;
create table products as
select distinct product
from have
;
data products;
set products;
do date='01jul2011'd to '18jul2011'd;
output;
end;
run;
data want;
merge have products;
by product date;
total=max(total,0);
run;
HTH,
Art
Or
data have; informat date date9.; input product date total; cards; 1 01jul2011 1 1 03jul2011 5 1 17jul2011 2 2 02jul2011 3 2 18jul2011 4 3 02jul2011 3 3 17jul2011 4 ; proc sql noprint; create table products as select distinct product from have ; quit; data products; set products; do date='01jul2011'd to '18jul2011'd; output; end; run; data want; merge have products; by product date; run; proc stdize data=want out=w missing=0 reponly; var total; run;
Ksharp
Thanks everyone, I always learn something new when I post in this forum.
Just what I needed.
Fred
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.