I have a dataset with amounts for May and June dates but some dates are missing. I would like to add the missing dates and later use lag function to add the missing amount
Have
ID DATE AMT
1 6/1/2023 $10
1 6/3/2023 $20
.
.
1 6/12/2023 $15
1 6/14/2023 $20
.
.
1 6/30/2023 $20
Want
ID DATE AMT
1 6/1/2023 $10
1 6/2/2023 $10
1 6/3/2023 $20
.
.
1 6/12/2023 $15
1 6/13/2023 $15
1 6/14/2023 $20
.
.
1 6/30/2023 $20
Can I have some logic to create `Want dataset
this is what I tried
Proc sql;
create table uniqueid as select
distinct Id from Have;
quit;
data tempdates;
strdate='01MAY2023'd;
enddate='30JUN2023'd;
do date=str to enddate;
output;
end;
run;
data merged;
merge Have tempdates;
run;
Use a cross-join...
proc sql ; create table all_id_dates as select a.id, b.date, c.amt from uniqueid a cross join tempdates b left join have c on a.id = c.id and b.date = c.date order by a.id, b.date ; quit ;