DATA Step, Macro, Functions and more

Importing CSV with different date formats

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Importing CSV with different date formats

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?


Accepted Solutions
Solution
‎08-15-2017 06:16 PM
Respected Advisor
Posts: 4,173

Re: Importing CSV with different date formats

Posted in reply to priscilabaddouh

@priscilabaddouh

To post a 3rd option which is kind-of in the middle of what @art297 and @KurtBremser 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...

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: Importing CSV with different date formats

Posted in reply to priscilabaddouh

I would try importing the field using the anydtdte. informat

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 7,866

Re: Importing CSV with different date formats

Posted in reply to priscilabaddouh

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";
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,492

Re: Importing CSV with different date formats

Posted in reply to priscilabaddouh

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

 

Solution
‎08-15-2017 06:16 PM
Respected Advisor
Posts: 4,173

Re: Importing CSV with different date formats

Posted in reply to priscilabaddouh

@priscilabaddouh

To post a 3rd option which is kind-of in the middle of what @art297 and @KurtBremser 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...

New Contributor
Posts: 3

Re: Importing CSV with different date formats

Thank you.
New Contributor
Posts: 3

Re: Importing CSV with different date formats

Posted in reply to priscilabaddouh

Thank you everyone.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 357 views
  • 1 like
  • 4 in conversation