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.
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;
Some example (both Have and Want) will help.
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
I noticed an error in what I pasted, I would not delete august, only july (null) and june(july - 1)
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;
Awesome, thank you for the help!
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.