Unable to convert all character values to date format mmddyy10.

[Denali]

Hi, 

I received a date variable (infection2_date) in Excel, but when I imported into SASm it showed as character fomat. I

[infection2_date Char 10 $10. $10. infection2_date.]

I ran the code below, but some dates were not successfully converted (see SAS data output). 
data want;
set test;
newdate = input(infection2_date,mmddyy10.);
format newdate mmddyy10.;
run;

Can anyone please help me with the code to convert all the dates from char values? Thanks in advance!

 

[Tom]

Since the column in your Excel worksheet had a mix of numeric (DATE) values and CHARACTER so SAS had to make the variable it used to store the values CHARACTER (you can store numbers as character strings but not the other way around).  When it does that it stores DATE values as the digit string that represents the number that Excel uses to store the number.

If you can modify the Excel sheet the find the cells with character strings (perhaps someone had a string in DDMMYY order that your version of Excel did not understand.  Or had an invalid date such as FEB29 on a non-leap year.)

Otherwise the fix is to first convert the string into a number, then adjust for the difference in how Excel and SAS number the days.  Make sure to attach a date style format.  I would avoid using MMDDYY as displaying dates in MDY order will confuse viewers from countries that normally display dates in DMY order.  So use either DATE or YYMMDD format to avoid such confusion.

data want;
  set test;
  newdate = input(infection2_date,32.) + '30DEC1899'd ;
  format newdate yymmdd10.;
run;