Hi I have a dataset "Results" that has the cost for each ID per month, along with some other variables (Class1, Class 2, OIC)
Because it is possible to have no cost for a particular month, I want to to fill in these missing rows for each ID, based on another table, "List" which is all of the months already passed for this financial year (starts at April 2019, up to present, January 2020). For the rest of the variables, I just want to retain the values from the row above.
If it is also possible to just start at the initial month in the original data, for example, ID 0002232 example should not have Apr-Aug data because data for it only started at Sep.
data Results;
input ID $ Class1 $ Class2 $ OIC $ Base_YM $ Cost;
datalines;
0901708 JP J Amanda 201904 56
0901708 JP J Amanda 201905 84
0901708 JP J Amanda 201906 26
0901708 JP J Amanda 201908 94
0901708 JP J Amanda 201910 47
0002232 NJP NJ Steve 201909 56
0002232 NJP NJ Steve 201911 84
5548763 NJP NJ Mark 201907 26
5548763 NJP NJ Mark 201908 94
5548763 NJP NJ Mark 201909 47
;
run;
data List;
input Base_YM $;
datalines;
201904
201905
201906
201907
201908
201909
201910
201911
201912
202001
;
run;
data want;
input ID $ Class1 $ Class 2 $ OIC $ Base_YM $ Cost;
datalines;
0901708 JP J Amanda 201904 56
0901708 JP J Amanda 201905 84
0901708 JP J Amanda 201906 26
0901708 JP J Amanda 201907 26
0901708 JP J Amanda 201908 94
0901708 JP J Amanda 201909 94
0901708 JP J Amanda 201910 47
0901708 JP J Amanda 201911 47
0901708 JP J Amanda 201912 47
0901708 JP J Amanda 202001 47
0002232 NJP NJ Steve 201909 56
0002232 NJP NJ Steve 201910 56
0002232 NJP NJ Steve 201911 84
0002232 NJP NJ Steve 201912 84
0002232 NJP NJ Steve 202001 84
5548763 NJP NJ Mark 201907 26
5548763 NJP NJ Mark 201908 94
5548763 NJP NJ Mark 201909 47
5548763 NJP NJ Mark 201910 47
5548763 NJP NJ Mark 201911 47
5548763 NJP NJ Mark 201912 47
5548763 NJP NJ Mark 202001 47
;
run;
... View more