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!
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;
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.
Doesn't work 😛
Can be fixed by replacing "le" with "lt".
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;
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.
Ready to level-up your skills? Choose your own adventure.