Fill in the missing dates and assign a value

[PatrykSAS]

Hi, I have day-by-day data where some dates are missing. I would like to add missing dates to a new row and assign them a value of 0 in a separate column. There are no duplicates in the data, and they are sorted from oldest to newest. It looks like below:

data have;
input date :yymmdd10. count;
format date yymmddd10.;
datalines;
2022-03-02 20
2022-03-03 24
2022-03-05 23
2022-03-07 15
;
run;

data want;
input date :yymmdd10. count;
format date yymmddd10.;
datalines;
2022-03-02 20
2022-03-03 24
2022-03-04 0
2022-03-05 23
2022-03-06 0
2022-03-07 15
;
run;

Do you know an easy way to quickly complete such a table?

[PeterClemmensen]

Do you have SAS/ETS?

[PeterClemmensen · Accepted solution]

If so then Proc Timeseries is the right tool

data have;
input date :yymmdd10. count;
format date yymmddd10.;
datalines;
2022-03-02 20
2022-03-03 24
2022-03-05 23
2022-03-07 15
;
run;

proc timeseries data = have out = want;
   id date interval   = day
           accumulate = none
           setmiss    = 0
           format     = yymmddd10.;
   var count;
run;

Result:

date        count 
2022-03-02  20 
2022-03-03  24 
2022-03-04  0 
2022-03-05  23 
2022-03-06  0 
2022-03-07  15  

[PatrykSAS]

This code did its job. Thanks for the help!

[PeterClemmensen]

Anytime 🙂

[Ksharp]

data have;
input date :yymmdd10. count;
format date yymmddd10.;
datalines;
2022-03-02 20
2022-03-03 24
2022-03-05 23
2022-03-07 15
;
run;

data want;
merge have have(keep=date rename=(date=_date) firstobs=2);
output;
do date=date+1 to coalesce(_date-1,0);
 count=0;output;
end;
drop _date;
run;