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 .
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.