Identify missing dates

Accepted Solution Solved
Reply
Valued Guide
Posts: 854
Accepted Solution

Identify missing dates

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.


Accepted Solutions
Solution
‎12-08-2014 05:07 PM
Respected Advisor
Posts: 3,124

Re: Identify missing dates

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Identify missing dates

Some example (both Have and Want) will help.

Valued Guide
Posts: 854

Re: Identify missing dates

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

Valued Guide
Posts: 854

Re: Identify missing dates

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

Solution
‎12-08-2014 05:07 PM
Respected Advisor
Posts: 3,124

Re: Identify missing dates

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;

Valued Guide
Posts: 854

Re: Identify missing dates

Awesome, thank you for the help!

Valued Guide
Posts: 854

Re: Identify missing dates

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 240 views
  • 0 likes
  • 2 in conversation