Missing Dates

[fred_major]

Hello I need a little help with missing Dates.

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

[Reeza]

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.

[art297]

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

[Ksharp]

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

[fred_major]

Thanks everyone, I always learn something new when I post in this forum.

Just what I needed.

Fred