topic Missing Dates in SAS Procedures

Missing Dates

fred_major — Mon, 18 Jul 2011 16:58:18 GMT

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

Missing Dates

Reeza — Mon, 18 Jul 2011 18:13:48 GMT

Create a data set that has the list of dates you need and then left join on the data sets.

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.

Missing Dates

art297 — Mon, 18 Jul 2011 19:01:59 GMT

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

Missing Dates

Ksharp — Tue, 19 Jul 2011 08:45:34 GMT

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

Missing Dates

fred_major — Tue, 19 Jul 2011 12:59:53 GMT

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

Just what I needed.

Fred