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
Tuesday
Respected Advisor
Posts: 3,895

Re: Importing CSV with different date formats

@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,363

Re: Importing CSV with different date formats

I would try importing the field using the anydtdte. informat

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 6,946

Re: Importing CSV with different date formats

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,363

Re: Importing CSV with different date formats

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
Tuesday
Respected Advisor
Posts: 3,895

Re: Importing CSV with different date formats

@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

Thank you everyone.

☑ This topic is SOLVED.

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

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