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?
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.
I would try importing the field using the anydtdte. informat
Art, CEO, AnalystFinder.com
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";
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
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.
Thank you everyone.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.