Adding rows based on dates

[shadow_lion]

Hi!

I have a dataset in the following format:

ID Value Created_date Updated_date

1     49           12-1-2020    12-1-2020

1     49           12-1-2020       1-1-2021

1     112          12-1-2020       2-1-2021

1     230        12-1-2020       4-1-2021

1     506        12-1-2020       6-1-2021

2    10             4-1-2020      4-1-2020

2    64            4-1-2020       7-1-2020

2    89            4-1-2020       8-1-2020

2    29            4-1-2020     10-1-2020

....

I would like to insert rows for the missing months in the updated_date column with the values from the previous non-missing row. It would look like this:

ID Value Created_date Updated_date

1     49           12-1-2020    12-1-2020

1     49           12-1-2020       1-1-2021

1     112          12-1-2020       2-1-2021

1     112          12-1-2020       3-1-2021

1     230        12-1-2020       4-1-2021

1     230        12-1-2020       5-1-2021

1     506        12-1-2020       6-1-2021

2    10             4-1-2020      4-1-2020

2    10             4-1-2020      5-1-2020

2    10             4-1-2020      6-1-2020

2    64            4-1-2020       7-1-2020

2    89            4-1-2020       8-1-2020

2    89             4-1-2020      9-1-2020

2    29            4-1-2020     10-1-2020

....

I used a do loop to come up with something close to my desired result but not exactly what I am looking for. Any ideas?

Thanks! 

[Kurt_Bremser]

Do a "look forward":

data want;
merge
  have
  have (
    firstobs=2
    keep=id updated_date
    rename=(id=_id updated_date=_updated)
  )
;
output;
if _id = id
then do;
  updated_date = intnx('month',updated_date,1,'b');
  do while (updated_date le _updated);
    output;
    updated_date = intnx('month',updated_date,1,'b');
  end;
end;
drop _id _updated;
run;

Untested, for tested code, supply example data in a working DATA step with DATALINES.

[andreas_lds]

Doesn't work 😛

Can be fixed by replacing "le" with "lt".

[Ksharp · Accepted solution]

data have;
input ID Value Created_date :mmddyy12. Updated_date :mmddyy12.;
format  Created_date  Updated_date mmddyyd10.;
cards;
1     49           12-1-2020    12-1-2020
1     49           12-1-2020       1-1-2021
1     112          12-1-2020       2-1-2021
1     230        12-1-2020       4-1-2021
1     506        12-1-2020       6-1-2021
2    10             4-1-2020      4-1-2020
2    64            4-1-2020       7-1-2020
2    89            4-1-2020       8-1-2020
2    29            4-1-2020     10-1-2020
;

data want;
 merge have have(keep=ID Updated_date rename=(ID=_ID Updated_date=_Updated_date) firstobs=2);
 output;
 if ID=_ID then do;
  do i=1 to intck('month',Updated_date,_Updated_date)-1;
    Updated_date=intnx('month',Updated_date,1);output;
  end;
 end;
drop i _:;
run;