struggle with different date format in a string

[Nasser_DRMCP]

Hello

data have;
format date_char $100. ;
date_char = '14/09/2035' ;
output;
date_char = '2035-07-02' ;
output ;
 
run;



data have2 ;
set have ;
format date_num1 date_num2 date9. ;
date_num1=input(date_char,ddmmyy10.);
date_num2=input(date_char,yymmdd10.); 
run ;

hello

I have a string column  that contains different format of dates.

my aim is to get a column that transforms char in a date column.

by using input, I get a missing value depending the input format.

How could I get a colum that always works (without missing) whatever the format date of my source column date_char

thanks in advance

Nasser

[FreelanceReinh]

Hello @Nasser_DRMCP,

You could try the ANYDTDTEw. informat:

date_num=input(date_char,anydtdte60.);

But read the documentation to make sure that the results in cases of ambiguous or incomplete date strings (e.g., '03-02-01' or 'APR15') meet your requirements.

Otherwise, try to identify the reason for the inconsistent DATE_CHAR values (e.g., different data sources, countries, etc.) and then use more specific informats conditional on source, country, etc.

[andreas_lds]

Your are sure that yymmdd10 is the correct informat to read  '2035-07-02'? It could be yyddmm10, too.

[dxiao2017 · Accepted solution]

Hi @Nasser_DRMCP , I agree with @FreelanceReinh 's suggestion, using anydtdte. to convert a character date column that has different date formats/informats for its values. The code and output is as follows (,and you do not need to create two columns for the numeric date value, creating one column is ok):

data have;
   format date_char $10.;
   date_char = '14/09/2035' ;output;
   date_char = '2035-07-02' ;output ;
run;
proc print data=have;run;
data have2 ;
   set have ;
   format date_num date9. ;
   date_num=input(date_char,anydtdte10.); 
run;
proc print data=have2;run;

The reason you got missing values in your output is that, for your date_num1 column, you asks SAS to read a character date value using ddmmyy. informat, however, 2035-07-02 is not that informat, so its correspondence value in date_num1 is missing, similariy, for your date_num2 column, you asks SAS to read a character date value using yymmdd. informat, however, 14/09/2035 is not that informat, so its correspondence value in date_num2 is missing. And therefore, the solution is using anydtdte. informat to let SAS read those values in your character date column with different informats, and creating only one numeric date column is sufficient enough to do this convertion.