Importing CSV with different date formats

[priscilabaddouh]

I am trying to import a CSV file that has a date variable. Some of the dates are in the yyyy-mm-dd format and others are in mm/dd/yyyy format. How can I import using infile without getting an error?

[art297]

I would try importing the field using the anydtdte. informat

Art, CEO, AnalystFinder.com

[Kurt_Bremser]

Read into a character variable, and then convert conditionally, depending on the position of the delimiters:

if substr(charvar,5,1) = '-' then datevar = input(charvar,yymmdd10.);
else if substr(charvar,3,1) = '/' then datevar = input(charvar,mmddyy10.);
else put "invalid date format detected";

[art297]

I'll preface my suggestion with first set the datestyle option to MDY so that SAS will know how to read ambiguous dates. e.g.:

options datestyle=mdy;
data want;
  input date anydtdte10.;
  format date date9.;
  cards;
2012-04-15
2014-5-6
6/8/2013
7/9/2014
08/12/2015
;

I prefer that over looking for particular characters in particular columns since, sometimes, people will enter 1 or 2 digit months or days.

Art, CEO, AnalystFinder.com

[Patrick · Accepted solution]

@priscilabaddouh

To post a 3rd option which is kind-of in the middle of what @art297 and @Kurt_Bremser suggest.

You could create your own informat using a regular expression and then use pattern specific informats. This is a bit more complicated than using the anydtdte. informat but it gives you full control over the treatment for different text patterns.

proc format;
  invalue mixedDates
    '/-/'  (regexp) = [yymmdd10.] 
    '/\//' (regexp) = [mmddyy10.] 
    other=_error_
  ;
run; 


data want;
  input date mixedDates.;
  format date date9.;
  cards;
2012-04-15
2014-5-6
6/8/2013
7/9/2014
08/12/2015
01jan2017
;
run;

Using a RegEx in an informat requires a current SAS version.

http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#n1jriq5xib5j45n1pwpwz...

[priscilabaddouh]

Thank you.

[priscilabaddouh]

Thank you everyone.