topic Re: Identify the missing date in series of date with in each group in SAS Programming

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

avinashns — Thu, 26 Aug 2021 17:52:21 GMT

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

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

sbxkoenk — Thu, 26 Aug 2021 18:06:41 GMT

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

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

sbxkoenk — Thu, 26 Aug 2021 18:10:30 GMT

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-using-PROC-EXPAND-and/ta-p/714806

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-continuity-of-your/ta-p/714678

Koen

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

mkeintz — Thu, 26 Aug 2021 18:23:17 GMT

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.

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

avinashns — Thu, 26 Aug 2021 19:04:37 GMT

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

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

Ksharp — Fri, 27 Aug 2021 13:32:28 GMT

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;