Filling in missing date values with a count of 0

[mitrakos]

Hello!

I am currently trying to do a frequency count for a range of dates. I've done the frequency using the weeku6 format using this code: 

proc freq data=work.test noprint;
	tables date1 / out=work.test2;
	format date1 weeku6.;
run;

This has given me an output of this (this is the beginning of the output):

What I would like to do is write some code that will fill in the missing weeks in-between the range of weeks I have (this specific dataset goes from 19W43 to 20W19, but I am working with different datasets that have different start and end dates) as well as give them a count of 0. Is there a way to do this? I hope I explained this clear enough. Thank you so much for reading!!

[Kurt_Bremser · Accepted solution]

Create some data for testing:

data test;
input date1 yymmdd10.;
format date1 yymmdd10.;
datalines;
2020-01-01
2020-01-03
2020-01-10
2020-01-12
2020-02-05
2020-02-07
;

Then, apply your code:

proc freq data=work.test noprint;
  tables date1 / out=work.test2;
  format date1 weeku6.;
run;

Then, use a "look-ahead" to fill the gaps:

data want;
merge
  test2
  test2 (
    firstobs=2
    keep=date1
    rename=(date1=_date1)
  )
;
output;
date1 = date1 + 7;
do while (week(date1) lt week(_date1));
  count = 0;
  percent = 0;
  output;
  date1 = date1 + 7;
end;
drop _date1;
run;

[mitrakos]

This worked beautifully thank you so much!!