topic Re: Fixing messy dates in SAS Programming

Fixing messy dates

rajd1 — Mon, 29 Jun 2020 21:19:44 GMT

I have dates that are messy. The missing values are coded as 99 for month and year.

The rule i want to implement is as follows:

Month missing/day missing/Year = 7/1/Year

Month/day missing/Year = Month/15/Year

Here is the dataset:

DateR

99/99/2006

8/99/2017

7/99/2018

6/99/2015

Thanks!!

Re: Fixing messy dates

ballardw — Mon, 29 Jun 2020 21:33:22 GMT

Do you have any with just Month missing (assume by "missing" you mean a value of 99 is recorded and that the expected order is MMDDYYYY)?

And including one actual valid date for comparison:

data example;
   input DateR :$10.;
   if scan(dater,2)='99' then do;
      if scan(dater,1)='99' then date=mdy(7,1,input(scan(dater,3),best.));
      else date= mdy(input(scan(dater,1),best.),15,input(scan(dater,3),best.));
   end;
   else date= input(dater,mmddyy10.);
   format date mmddyy10.;
datalines;
99/99/2006
99/99/2006
8/99/2017
8/99/2017
8/99/2017
8/99/2017
7/99/2018
7/99/2018
6/99/2015
4/12/2012
;

Re: Fixing messy dates

Reeza — Mon, 29 Jun 2020 21:35:27 GMT

if dateR =: '99/99' then do;
date_want = mdy(7, 1, input(scan(dater, 3, '/'), 8.));
end;
else if scan(dater, 2, '/') = '99' then do;
date_want = mdy(input(scan(dater, 1, '/'), 8.), 15, input(scan(dater, 3, '/'), 8.));
end;
else date_want = input(date4, mmddyy10.);

Re: Fixing messy dates

rajd1 — Tue, 30 Jun 2020 00:46:28 GMT

Works perfect!