Create date between consecutive dates-CodePudding

I hope you can assist.

I have a SAS data set which has two columns, ID and Date which looks like this: In some instances, the date column skips a month. I need a code which will create the missing date for each ID e.g. for AY273, I need a code that will create date 2022/11/20 and for WG163, 2022/12/15.

CodePudding user response：

Try this

data WANT (drop = this_date last_date);
    set HAVE(rename=(date = this_date));
    by id;
    last_date = lag(this_date);
    if first.id then do;
        date = this_date;
        output;
    end;
    else do date = this_date to last_date   16 by -30;
        output;
    end;

   format date yymmdd10.;
proc sort;
    by id date;
run;

If it does not work, I will correct it.

CodePudding user response：

You can merge the data with itself shifted one observation forward (to get a lead value) and loop across that range.

Example:

data have;
input id $ date yymmdd10.;
format date yymmdd10.;
datalines;
AAAAA 2021-11-20
AY273 2022-10-20
AY273 2022-12-20
AY273 2023-01-20
WG163 2022-10-15
WG163 2022-11-15
WG163 2023-01-15
ZZZZZ 2022-01-15
;

data want(keep=id date fillflag);
  merge have have(rename=(date=leaddate id=leadid) firstobs=2);

  if id eq leadid then
    do while (intck('month',date,leaddate) > 0);
      output;

      date = intnx('month',date,1,'sameday');
      fillflag = 1;
    end;
  else
    output;
run;