topic Re: Adding rows based on dates in SAS Programming

Adding rows based on dates

shadow_lion — Tue, 01 Aug 2023 01:21:29 GMT

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!

Re: Adding rows based on dates

Kurt_Bremser — Tue, 01 Aug 2023 05:55:08 GMT

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.

Re: Adding rows based on dates

andreas_lds — Tue, 01 Aug 2023 06:41:43 GMT

Doesn't work 😛

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

Re: Adding rows based on dates

Ksharp — Tue, 01 Aug 2023 11:45:18 GMT

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;