topic Re: Fill in the missing dates and assign a value in SAS Programming

Fill in the missing dates and assign a value

PatrykSAS — Mon, 07 Mar 2022 09:19:26 GMT

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?

Re: Fill in the missing dates and assign a value

PeterClemmensen — Mon, 07 Mar 2022 09:50:03 GMT

Do you have SAS/ETS?

Re: Fill in the missing dates and assign a value

PeterClemmensen — Mon, 07 Mar 2022 09:55:46 GMT

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

Re: Fill in the missing dates and assign a value

PatrykSAS — Mon, 07 Mar 2022 09:58:38 GMT

This code did its job. Thanks for the help!

Re: Fill in the missing dates and assign a value

PeterClemmensen — Mon, 07 Mar 2022 10:02:54 GMT

Anytime 🙂

Re: Fill in the missing dates and assign a value

Ksharp — Tue, 08 Mar 2022 12:15:02 GMT

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;