How to insert dates between 2 dates in column to make dates continuous (with changes in columns)

[adjn258]

Need help with inserting rows between 2 dates in a column to fill missing periods. Also the below columns:

1) value '0' in a pre-existing column (Column A) where new rows were added

2) a column to indicate where new rows which were added (Column B)

Dataset (This is just an illustration, the dataset is huge with many customers)

   Customer            Date                   Column A    

             1            Jan'2018                   123                

             1            May2018                   456

             1            July'2018                   567

             2            Mar'2019                    0 

             2            Jun'2019                   789

Desired dataset:

   Customer     Date           Column A      Column B

        1            Jan'2018          123                 .

        1            Feb'2018            0                New

        1            Mar'2018            0                New

        1            Apr'2018             0                New

        1           May'2018          456                 .

        1           Jun'2018             0                 New

        1           Jul'2018            567                 .

        2           Mar'2019             0                  .

        2            Apr'2019             0               New

        2           May'2019            0               New

        2           Jun'2019            789               .

[mkeintz]

This is untested since your sample data in not in the form of a working data step:

data want (drop=nxt_:);
  set have;
  by customer;
  output;
  if end_of_have=0 then set have (firstobs=2 keep=date rename=(date=nxt_date)) end=end_of_have;
  do while (last.customer=0 and intck('month',date,nxt_date)>1);
    date=intnx('month',date,1);
    columna=0;
    columnb='New';
    output;
  end;
run;

After outputting the record in hand, this program peeks at the date of the next record via the firstobs=2 option).  If there is a multi-month forward gap (and the next record has the same customer number), it then outputs a monthly series of dates with columna=0, until that fill-in process stops one month prior to the next record.

Please realize that this only fills internal "holes".  It does nothing about cases in which the final dates for an ID are missing.

The program also assumes that the variable DATE is stored with sas date values (internal value is number of days since 01jan1960).  That is what allows the intnx and intck functions to increment dates by month and to measure intervals in units of months respectively.

[Ksharp]

data have;
input Customer            Date    : monyy7.               ColumnA   ;
format date monyy7.;
cards; 
             1            Jan2018                   123
             1            May2018                   456
             1            Jul2018                   567
             2            Mar2019                    0 
             2            Jun2019                   789
;


data want;
 merge have have(keep=Customer  Date rename=(Customer=_Customer  Date=_Date) firstobs=2);
 output;
 if Customer=_Customer then do;
   do i=1 to intck('month',Date,_Date)-1;
     Date=intnx('month',Date,1);ColumnA=0;output;
   end;
 end;
drop _: i;
run;