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

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

adjn258 — Wed, 23 Mar 2022 14:53:43 GMT

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 .

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

mkeintz — Wed, 23 Mar 2022 15:53:20 GMT

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.

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

Ksharp — Thu, 24 Mar 2022 11:45:17 GMT

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;