topic Re: Read in file with multiple date formats in SAS Programming

Read in file with multiple date formats

sas_nut — Thu, 23 Jul 2020 14:46:49 GMT

Hi, I have a text file with a date with multiple date formats. Kind of what's created below in the sample data. I also created a picture format for the date format I need (further below). What I can't figure out is how to conditionally read in the file and maintain the data as a date. The final output needs to have the new date format (newdate.). I'm trying to avoid reading it in as a string then converting it later. Any ideas?

Thanks!

*sample data;

data test;
input date $10.;
datalines;
01JUL2020
2020-07-01
;
run;

*new data format.

proc format;
picture NewDate other='%Y-%0m-%0d' (datatype=date);
run;
%let now=%sysfunc(today(),newdate.);
%put &now;

Re: Read in file with multiple date formats

Kurt_Bremser — Thu, 23 Jul 2020 14:56:19 GMT

No need for custom formats:

data test;
input date anydtdte10.;
format date yymmddd10.;
datalines;
01JUL2020
2020-07-01
;

Although I would rather have (preferred in this order):

a consistent date format
a limited, exactly specified list of acceptable date formats, which can be checked
the current garbage that WILL lead to a problem some time in the future

Re: Read in file with multiple date formats

sas_nut — Thu, 23 Jul 2020 14:59:06 GMT

Thank you!

Re: Read in file with multiple date formats

ballardw — Thu, 23 Jul 2020 15:03:13 GMT

If you have two-digit years in the first position they may be treated as dd-mm-yy instead of yy-mm-dd values OR mm-dd-yy, national language rules, and may have missing values using the ANYDTDTE infromat.

So double check any results with 2 digit years, if any, from your source file, and the range of result for sensibility. If you end up asking "how did that date get in there" these are candidates for odd values in your input and the coding rules for ANYDTDTE