date conversion

[harcluna]

Hello,

I'm trying to organise a lot of dates into a standard format e.g. July 6, 1985. However, I've been sent dates in non-SAS format. This is my code so far, but I can't find the right informat for SAS to convert it to the standard format. The output I'd like to have for my code examples is July 4, 2015 and July 6, 2014.

Thank you very much

data practice;

input #1 @1 dates$12.;
Birth_date = input(dates,informat.);
datalines;
20154Jul
0762014
;

ods listing;
proc print data=practice;
run;
proc print data=practice;
format Birth_date worddate.;
run;

[Tom]

There are no informats that can read those values as dates. Either read it as a string and convert with code or create your own custom informat.  It might be hard to define a format that handles every possible input string. So perhaps a better approach is to just analyze the data you have and see what values existing (like the two you posted) that are confusing and then generate a format that handles those specific values.

So for example you could start by trying to convert your strings using the ANYDTDTE forma.

data test;
  input str $12. ;
  date = input(str,??anydtdte12.);
  format date yymmdd10. ;
datalines;
2015-07-04
04JUL2015
4jul15
07042015
20154Jul
0742014
;

You could then see what strings did not yield valid dates. For example you could look for missing, but you could also look for really strnage values like way in the past or far into the future.

proc freq order=data ;
 where missing(date);
 tables str ;
run;

Then you can decide what date you want those strings to mean and add them into a custom format.

proc format ;
 invalue mydt (upcase default=40)
   '20154JUL'='04JUL2015'd
   '0742014' ='04JUL2014'd
   other=[anydtdte.]
 ;
run;

Then you can use that to convert your strings to dates.

data want;
  input str $12. ;
  date = input(str,??mydt12.);
  format date yymmdd10. ;
datalines;
2015-07-04
04JUL2015
4jul15
07042015
20154Jul
0742014
;
proc print;
run;

Obs    str                 date

 1     2015-07-04    2015-07-04
 2     04JUL2015     2015-07-04
 3     4jul15        2015-07-04
 4     07042015      2015-07-04
 5     20154Jul      2015-07-04
 6     0742014       2014-07-04