topic Re: Importing CSV with different date formats in SAS Programming

Importing CSV with different date formats

priscilabaddouh — Fri, 11 Aug 2017 17:57:14 GMT

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?

Re: Importing CSV with different date formats

art297 — Fri, 11 Aug 2017 18:07:46 GMT

I would try importing the field using the anydtdte. informat

Art, CEO, AnalystFinder.com

Re: Importing CSV with different date formats

Kurt_Bremser — Fri, 11 Aug 2017 18:18:41 GMT

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";

Re: Importing CSV with different date formats

art297 — Fri, 11 Aug 2017 18:35:17 GMT

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

Re: Importing CSV with different date formats

Patrick — Sat, 12 Aug 2017 01:06:26 GMT

@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#n1jriq5xib5j45n1pwpwzk311v0p.htm

Re: Importing CSV with different date formats

priscilabaddouh — Tue, 15 Aug 2017 22:17:18 GMT

Thank you.

Re: Importing CSV with different date formats

priscilabaddouh — Tue, 15 Aug 2017 22:18:08 GMT

Thank you everyone.