Filling dates for the missing ones with future dates in Data set

[vickys]

Hi frds,

I have requirement to fill the missing dates with next month dates here is the example.

I have dataset

EMP_ID    KEY     Year    Month    Office    Date 

101            1         2016      Jan       A       03JAN2016
101            2         2016      Feb        A     15FEB2016
101            3         2016       Mar       A      
101            4         2016      Apr        A      
101            5         2016       May      A      20MAY2016 
101            6         2016       Jun       A      
101            7         2016        Jul       A     
101            8         2016       Aug      A     15AUG2016
101            9         2016       Sep      A      
101           10        2016       Oct       A     
101           11        2016      Nov       A      20NOV2016

I want the data to be like

EMP_ID    KEY     Year    Month    Office    Date

101            1         2016      Jan       A       03JAN2016
101            2         2016      Feb        A     15FEB2016
101            3         2016       Mar       A      20MAY2016
101            4         2016      Apr        A      20MAY2016
101            5         2016       May      A      20MAY2016
101            6         2016       Jun       A     15AUG2016
101            7         2016        Jul       A     15AUG2016
101            8         2016       Aug      A     15AUG2016
101            9         2016       Sep      A      20NOV2016
101           10        2016       Oct       A     20NOV2016
101           11        2016      Nov       A      20NOV2016

Please help..

Thanks in advance.
Vicks

[novinosrin]

data have;
infile cards truncover;
input  
(EMP_ID    KEY     Year    Month    Office) ($)   Date :date9.;
format date date9.;
cards;
101            1         2016      Jan       A       03JAN2016
101            2         2016      Feb        A     15FEB2016
101            3         2016       Mar       A      
101            4         2016      Apr        A      
101            5         2016       May      A      20MAY2016 
101            6         2016       Jun       A      
101            7         2016        Jul       A     
101            8         2016       Aug      A     15AUG2016
101            9         2016       Sep      A      
101           10        2016       Oct       A     
101           11        2016      Nov       A      20NOV2016
;


data want;
array t(999)_temporary_;
call missing(of t(*),n);
do until(last.emp_id);
set have;
by emp_id;
if not first.emp_id and missing(lag(date)) and date then do;
n+1;
t(n)=date;
end;
end;
do until(last.emp_id);
set have;
by emp_id;
if missing(date) then date=coalesce(of t(*));
else if date=coalesce(of t(*)) then do;
k=coalesce(of t(*));
k1=whichn(k, of t(*));
call missing(t(k1));
end;
output;
end;
drop n k:;
run;

[vickys]

Many Thanks, Novinnosrim.

Its working 

But its my mistake as didnt explained it clearly. The data also includes different years 2017 and 2018 like below.

(EMP_ID KEY Year Month Office) ($) Date :date9.;
format date date9.;
cards;
101 1 2016 Jan A 03JAN2016
101 2 2016 Feb A 15FEB2016
101 3 2016 Mar A
101 4 2016 Apr A
101 5 2016 May A 20MAY2016
101 6 2016 Jun A
101 7 2016 Jul A
101 8 2016 Aug A 15AUG2016
101 9 2016 Sep A
101 10 2016 Oct A
101 11 2016 Nov A 20NOV2016
101 1 2016 Jan A 03JAN2017
101 2 2016 Feb A 15FEB2017
101 3 2016 Mar A
101 4 2016 Apr A
101 5 2016 May A 20MAY2017
101 1 2016 Jan A
101 2 2016 Feb A 15FEB2018
101 3 2016 Mar A
101 4 2016 Apr A
101 5 2016 May A 20MAY2018

 Data expecting:

101 1 2016 Jan A 03JAN2016
101 2 2016 Feb A 15FEB2016
101 3 2016 Mar A 20MAY2016 
101 4 2016 Apr A 20MAY2016 
101 5 2016 May A 20MAY2016 
101 6 2016 Jun A  15AUG2016
101 7 2016 Jul A  15AUG2016
101 8 2016 Aug A 15AUG2016
101 9 2016 Sep A 20NOV2016
101 10 2016 Oct A 20NOV2016
101 11 2016 Nov A 20NOV2016 
101 1 2016 Jan A 03JAN2017
101 2 2016 Feb A 15FEB2017
101 3 2016 Mar A  20MAY2017
101 4 2016 Apr A  20MAY2017
101 5 2016 May A 20MAY2017
101 1 2016 Jan A 15FEB2018
101 2 2016 Feb A  15FEB2018
101 3 2016 Mar A  20MAY2018
101 4 2016 Apr A  20MAY2018
101 5 2016 May  A  20MAY2018

Really appreciate.

Regards,

Vikcys

[novinosrin]

I can notice the date value you modifed with some 2017,18 dates and your year col is all 2016

That's got nothing to do with how the code works

[Astounding · Accepted solution]

With a little sorting, this becomes an easy problem:

proc sort data=have;

   by Emp_ID descending key;

run;

data want;

set have;

by emp_ID;

retain last_date;

if first.emp_ID then last_date=.;

if date > . then last_date = date;

else date = last_date;

drop last_date;

run;

Then optionally:

proc sort data=have;

   by Emp_ID key;

run;

By changing the order, you can fill with the most recent nonmissing value, instead of searching for a future nonmissing value ... a much easier problem.

[novinosrin]

Hi @vickys  While I await for clarity on your HAVE, I would suggest you to take @Astounding approach as that is much easier to understand and implement and maintain. It's difficult for us to assume your comfort level with concepts like DOW etc, nevertheless better to be safe than sorry.