topic Filling in missing date values with a count of 0 in New SAS User

Filling in missing date values with a count of 0

mitrakos — Wed, 14 Oct 2020 13:20:43 GMT

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!!

Re: Filling in missing date values with a count of 0

Kurt_Bremser — Wed, 14 Oct 2020 13:44:43 GMT

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;

Re: Filling in missing date values with a count of 0

mitrakos — Wed, 14 Oct 2020 16:23:53 GMT

This worked beautifully thank you so much!!