Help using Base SAS procedures

Missing Dates

Reply
Contributor
Posts: 38

Missing Dates

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

Super User
Posts: 17,868

Missing Dates

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.

PROC Star
Posts: 7,363

Missing Dates

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

Super User
Posts: 9,682

Missing Dates

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

Contributor
Posts: 38

Missing Dates

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

Just what I needed.

Fred

Ask a Question
Discussion stats
  • 4 replies
  • 249 views
  • 0 likes
  • 4 in conversation