topic Re: Identify missing dates in SAS Programming

Identify missing dates

Steelers_In_DC — Mon, 08 Dec 2014 21:14:12 GMT

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.

Re: Identify missing dates

Haikuo — Mon, 08 Dec 2014 21:36:50 GMT

Some example (both Have and Want) will help.

Re: Identify missing dates

Steelers_In_DC — Mon, 08 Dec 2014 21:50:44 GMT

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

Re: Identify missing dates

Steelers_In_DC — Mon, 08 Dec 2014 21:51:54 GMT

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

Re: Identify missing dates

Haikuo — Mon, 08 Dec 2014 22:07:24 GMT

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;

Re: Identify missing dates

Steelers_In_DC — Mon, 08 Dec 2014 22:07:58 GMT

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;

Re: Identify missing dates

Steelers_In_DC — Mon, 08 Dec 2014 22:40:44 GMT

Awesome, thank you for the help!