BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Nasser_DRMCP
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
dxiao2017
Lapis Lazuli | Level 10

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;

dxiao2017_0-1756982181125.png

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.

View solution in original post

3 REPLIES 3
FreelanceReinh
Jade | Level 19

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
Jade | Level 19

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

 

dxiao2017
Lapis Lazuli | Level 10

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;

dxiao2017_0-1756982181125.png

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 359 views
  • 2 likes
  • 4 in conversation