Identify missing dates

[Steelers_In_DC]

I am reconciling a large amounts of accounts.  I come across many that, over time have missing records.  I want to ignore the month before a missing date because I'm unable to reconcile it, not ignore but move it into a separate dataset, any help will be appreciated. I've been trying a few different things starting to move in circles.  My mind keeps going to if intnx('month',date,1,'e') exists then output bad; else output good;  I understand that doesn't work but that's what I want to get done.

[Haikuo]

Some example (both Have and Want) will help.

[Steelers_In_DC]

I pasted an example below.  I've used proc expand to update the dataset, filling in missing values with dates and nulls.  Now I would like to delete the record where time_key = . but also delete the record prior.  The missing record makes it impossible to reconcile the previous record.

I hope this helps.

have

date  time_key

10/01/07 20071031
09/01/07 20070930
08/01/07 20070831
07/01/07 .
06/01/07 20070630
05/01/07 20070531
04/01/07 20070430

want

date  time_key

10/01/07 20071031
09/01/07 20070930
06/01/07 20070630
05/01/07 20070531
04/01/07 20070430

[Steelers_In_DC]

I noticed an error in what I pasted, I would not delete august, only july (null) and june(july - 1)

[Haikuo · Accepted solution]

So if your data is presorted as being showed here:

data have;

     input date:mmddyy10.  time_key;

     format date mmddyy10.;

     cards;

10/01/07 20071031

09/01/07 20070930

08/01/07 20070831

07/01/07 .

06/01/07 20070630

05/01/07 20070531

04/01/07 20070430

;

data want;

     set have;

     retain n;

     if missing(time_key) then

           n=_n_;

     if n<=_n_<=n+1 then

           delete;

     drop n;

run;

[Steelers_In_DC]

Awesome, thank you for the help!

[Steelers_In_DC]

Not sure if this will make sense but here's what I want:

data final_exp;

set exp;

n+1;

if time_key = . then do;

     delete %eval(n+1);

end;

run;