Identify the missing date in series of date with in each group

[avinashns]

I have a data set  with respect to dates as below.

I would like to identify and insert the missing date series in each group and rest of the variables value should be previous record data.

Data have:

Date                 Employee    var2    var3    var4 ..... ...

01aug2021      Tom

02aug2021      Tom

04aug2021      Tom

05aug2021      Tom

06aug2021      Tom

01Sep2021      John

03sep2021      John

01Sep2021      John

As you can see 03aug2021 is missing in under TOM and 02Sep2021 is missing from John group.

Data Want:

Date                 Employee    var2    var3    var4 ..... ...

01aug2021      Tom

02aug2021      Tom

03aug2021      Tom

04aug2021      Tom

05aug2021      Tom

06aug2021      Tom

01Sep2021      John

02Sep2021      John

03sep2021      John

01Sep2021      John

[sbxkoenk]

Hello,

I have only a minute but, if not solved by tomorrow, I can elaborate on this topic.

For the missing dates, my colleague Gerhard Svolba has published numerous community library articles on this topic, using the SAS/ETS procedures PROC TIMESERIES or PROC EXPAND or PROC TIMEDATA and maybe also PROC TIMEID (??). See the Communities Library.

The missing value imputation method you are looking after is called "Last Value Carry Forward" (LVCF) or "forward-filling". Search these communities with those keywords and you will find numerous hits (numerous topics on this subject).

Good luck,

Koen

[sbxkoenk]

On top of my previous reply (see above) ...

Replace MISSING VALUES in TIMESERIES DATA using PROC EXPAND and PROC TIMESERIES
Posted 02-04-2021 10:45 AM | by gsvolba
https://communities.sas.com/t5/SAS-Communities-Library/Replace-MISSING-VALUES-in-TIMESERIES-DATA-usi...

Using the TIMESERIES procedure to check the continuity of your timeseries data
Posted 01-28-2021 03:40 PM | by gsvolba
https://communities.sas.com/t5/SAS-Communities-Library/Using-the-TIMESERIES-procedure-to-check-the-c...

Koen

[avinashns]

Hi,
Even if we are able get what are the missing dates between the max and min dates of each employee that will also be fine later we can fill other values. like
03aug2021 Tom * Employee Tom missing one date
02Sep2021 John * employee John missing one date
05Sep2021 Dave * employee Dave missing two date
08Sep2021 Dave * employee Dave missing two date ... etc
Thanks in advance

[mkeintz]

You basically want to carry forward a given record over any internal "holes" in the series:

data want (drop=nxt_:);
  set have (keep=employee);
  by employee;
  merge have  have (firstobs=2 keep=date rename=(date=nxt_date));
  output;
  if last.employee=0 then do while (date<nxt_date-1);
      date=date+1;
    output; 
  end;
run;

The first SET statement is there just to generate the first.employee and last.employee dummies for testing in an IF statement.  

[Ksharp · Accepted solution]

data have;
input Date     : date9.            Employee  $;
format date date9.;
cards;
01aug2021      Tom
02aug2021      Tom
04aug2021      Tom
05aug2021      Tom
06aug2021      Tom
01Sep2021      John
03sep2021      John
04Sep2021      John
;

data want;
 merge have have(firstobs=2 keep=date employee rename=(date=_date employee=_employee));
 output;
 if employee=_employee then do;
  do date=date+1 to _date-1;
   output;
  end;
 end;
drop _:;
run;